﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Drawing;
using System.IO;
using System.Diagnostics;
using System.Reflection;
using System.Net.Mail;
using Excel = Microsoft.Office.Interop.Excel;
using Npgsql;

namespace appLibrary
{
   public class Util
    {

        public static void ClearControl(Control root)
        {
            foreach (Control ctrl in root.Controls)
            {
                ClearControl(ctrl);
                if (ctrl is TextBox)
                {
                    ((TextBox)ctrl).Text = string.Empty;
                }
                
            }
        }


        public static void ClearControlDgv(Control root)
        {
            foreach (Control ctrl in root.Controls)
            {
                ClearControl(ctrl);
                if (ctrl is DataGridView)
                {
                    ((DataGridView)ctrl).Rows.Clear();
                }

            }
        }


        public static int getPosition(DataTable dt, string strValue)
        {
            int intPosCurrent = -1;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (dt.Columns[i].ColumnName.Equals(strValue))
                {
                    intPosCurrent = i;
                    break;
                }

            }

            return intPosCurrent;


        }

        public static int getPosition(DataGridView dgv, string strValue)
        {

            int intPosCurrent = -1;
            for (int i = 0; i < dgv.Columns.Count; i++)
            {
                if (dgv.Columns[i].Name.Equals(strValue))
                {
                    intPosCurrent = i;
                    break;
                }

            }

            return intPosCurrent;


        }

        public static int getPositionCol(DataTable dt, string strValue)
        {

            int intPosCurrent = -1;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (dt.Columns[i].ColumnName.Equals(strValue))
                {
                    intPosCurrent = i;
                    break;
                }

            }

            return intPosCurrent;


        }

        public static int getPositionRow(DataTable dt, int posCol, string strValue, int intPosEnd)
        {

            int intPosCurrent = -1;
            for (int i = 0; i < dt.Columns.Count - intPosEnd; i++)
            {
                if (dt.Rows[i][posCol].Equals(strValue))
                {
                    intPosCurrent = i;
                    break;
                }

            }

            return intPosCurrent;


        }
        public static int getPositionRow(DataTable dt, string nameCol, string strValue)
        {

            int intPosCurrent = -1;
            for (int i = 0; i < dt.Rows.Count ; i++)
            {
                if (dt.Rows[i][nameCol].Equals(strValue))
                {
                    intPosCurrent = i;
                    break;
                }

            }

            return intPosCurrent;


        }


        public static int getPositionRow(DataTable dt, string nameCol1, string nameCol2, string strValue1, string strValue2)
        {

            int intPosCurrent = -1;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i][nameCol1].Equals(strValue1) && dt.Rows[i][nameCol2].Equals(strValue2))
                {
                    intPosCurrent = i;
                    break;
                }

            }

            return intPosCurrent;


        }

        public static int getPositionRow(DataGridView  dgv, int posCol, string strValue, int intPosEnd)
        {

            int intPosCurrent = -1;
            for (int i = posCol; i < dgv.Columns.Count - intPosEnd; i++)
            {
                if(dgv.Columns[i].HeaderText.Equals(strValue)){
                   intPosCurrent = i;
                    break;
                }
                

            }

            return intPosCurrent;


        }

        public static bool  isRowExist(DataTable dt, int posCol, string strValue)
        {

           
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i][posCol].Equals(strValue))
                {

                    return true;
                }

            }

            return false;


        }


        public static bool isnumeric(string strValue)
        {

            try
            {
                Convert.ToInt32(strValue);
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool isnumeric(object strValue)
        {

            try
            {
                Convert.ToInt32(strValue);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public static bool isDouble(string strValue)
        {

            try
            {
                Convert.ToDouble(strValue);
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool isDouble(object strValue)
        {

            try
            {
                Convert.ToDouble(strValue);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public static bool isDecimal(string strValue)
        {

            try
            {
                Convert.ToDecimal(strValue);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public static bool isDateTime(string strValue)
        {

            try
            {
                Convert.ToDateTime(strValue);
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool isString(object strValue)
        {

            try
            {
                Convert.ToString(strValue);
                return true;
            }
            catch
            {
                return false;
            }
        }

        
        public static bool isArrayString(object strValue)
        {

            try
            {
                string[] array = (string[])strValue;
                return true;
            }
            catch
            {
                return false;
            }
        }
        public static bool isArrayInteger(object strValue)
        {

            try
            {
                int[] array = (int[])strValue;
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool isArrayBytes(object objValue)
        {

            try
            {
               byte[] bytes= (byte[])objValue;
                return true;
            }
            catch
            {
                return false;
            }
        }


        public static void setFormatDgv(ref DataGridView dgv, int intPosCol)
        {

            for (int i = 0; i < dgv.Rows.Count; i++)
            {
                if (dgv.Rows[i].Cells[intPosCol].Value.Equals("0"))
                {
                    dgv.Rows[i].DefaultCellStyle.BackColor = Color.SkyBlue;//Color.FromArgb(209, 227, 254);
                    dgv.Rows[i].Cells[intPosCol].Value = "Con Errores";

                }
                else
                {
                    dgv.Rows[i].Cells[intPosCol].Value = "Satisfactorio";

                }
            }

          


        }
        public void getFormatDgv(ref DataGridView dgv)
        {

            for (int i = 0; i < dgv.Rows.Count; i++)
            {
                if ((i % 2) == 0)
                {
                    dgv.Rows[i].DefaultCellStyle.BackColor = Color.LightSkyBlue;//Color.FromArgb(209, 227, 254);
                    dgv.Rows[i].Cells[0].Style.BackColor = Color.White;//Cambiamos el color de la celda donde se encuentra el codigo canal.
                }

            }


        }

        public static void setFormatDgv(ref DataGridView dgv, int intPosCol, string strSuccessful, string strNoSuccessful)
        {

            for (int i = 0; i < dgv.Rows.Count; i++)
            {
                if (dgv.Rows[i].Cells[intPosCol].Value.Equals("0") || dgv.Rows[i].Cells[intPosCol].Value.Equals("Con Errores"))
                {
                    dgv.Rows[i].DefaultCellStyle.BackColor = Color.SkyBlue;//Color.FromArgb(209, 227, 254);
                    dgv.Rows[i].Cells[intPosCol].Value = strNoSuccessful;

                }
                else
                {
                    dgv.Rows[i].Cells[intPosCol].Value = strSuccessful;

                }
            }




        }

        public static void onlyDecimalNumber(ref TextBox textBox, KeyPressEventArgs e)
        {

            if (Char.IsDigit(e.KeyChar))
            {
                e.Handled = false;
                return;
            }
            if (e.KeyChar == 46 || e.KeyChar == 8)
            {
                int pos = textBox.Text.IndexOf('.');
                if (e.KeyChar != 8 && pos > 0)
                {
                    e.Handled = true;
                    return;
                }

                e.Handled = false;
                return;
            }

            else if (e.KeyChar == 'b')
            {
                e.Handled = false;
                return;
            }
            else
            {
                e.Handled = true;
                return;
            }
        }

        public static void onlyNumber(ref TextBox textBox, KeyPressEventArgs e)
        {

            if (Char.IsDigit(e.KeyChar))
            {
                e.Handled = false;
                return;
            }
            if (e.KeyChar == 46 || e.KeyChar == 8)
            {
                
                if (e.KeyChar != 8 )
                {
                    e.Handled = true;
                    return;
                }

                e.Handled = false;
                return;
            }

            else if (e.KeyChar == 'b')
            {
                e.Handled = false;
                return;
            }
            else
            {
                e.Handled = true;
                return;
            }
        }

        public static void numerPoint(ref TextBox textBox)
        {
            if (textBox.Text.Length > 0)
            {
                if (textBox.Text.Substring(0, 1).Equals(".") && textBox.Text.Length > 1)
                    textBox.Text = 0 + textBox.Text;
                else if (textBox.Text.Substring(0, 1).Equals(".") && textBox.Text.Length == 1)
                    textBox.Text = 0 + textBox.Text + 0;
                else if (!textBox.Text.Substring(0, 1).Equals(".") && textBox.Text.Length > 1 &&
                   textBox.Text.Substring(textBox.Text.Length - 1, 1).Equals("."))
                    textBox.Text = textBox.Text = textBox.Text + 0;
            }
        }




        public static  byte[] SaveFile(string productImageFilePath)
        {
                try
                {
                    FileStream pgFileStream = new FileStream(productImageFilePath, FileMode.Open, FileAccess.Read);

                    BinaryReader pgReader = new BinaryReader(new BufferedStream(pgFileStream));
                        
                            byte[] pgByteA = pgReader.ReadBytes(Convert.ToInt32(pgFileStream.Length));

                            return pgByteA;
                    
                }
                catch
                {
                    return null;
                    throw;
                }
            
        }

        public static byte[] imageToByteArray(Image imageIn)
        {
            MemoryStream ms = new MemoryStream();
            imageIn.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
            return ms.ToArray();
        }

       /*
        * Convierte un objeto a Byte[]  para luego obtener la imagen
        */
        public static Image getImage(object objFile)
        {
                        
                        try
                        {


                            if (objFile != null)
                            {
                                Byte[] productImageByte = (Byte[])objFile;
                                if (productImageByte != null)
                                {
                                    Stream productImageStream = new System.IO.MemoryStream(productImageByte);
                                    return Image.FromStream(productImageStream);
                                }
                            }
                        }
                        catch
                        {
                            throw;
                        }
                
            
            return null;
        }


        public static FileStream getFile(object objFile)
        {

            try
            {


                if (objFile != null)
                {
                    Byte[] fileByte = (Byte[])objFile;
                    if (fileByte != null)
                    {
                        Stream stream = new System.IO.MemoryStream(fileByte);
                        StreamReader reader = new StreamReader(stream);
                        return null;//new  FileStream(reader,
                      }        
                         //Image.FromStream(productImageStream);
                    
                }
            }
            catch
            {
                throw;
            }


            return null;
        }


        public static string  stringTokenizer(string strCadena, char chrDelimeter)
        {
            char[] delimeterChars = { chrDelimeter };
            try
            {
                string[] arrCadena = strCadena.Split(delimeterChars);
                if (arrCadena.Length > 0)
                {
                    return arrCadena[arrCadena.Length - 1];
                }
               
            }
            catch
            {
                throw;
            }

            return null;

        }
        public static string stringTokenizer(string strCadena, char chrDelimeter, int pos)
        {
            char[] delimeterChars = { chrDelimeter };
            try
            {
                string[] arrCadena = strCadena.Split(delimeterChars);
                if (arrCadena.Length > 0)
                {
                    return arrCadena[pos];
                }

            }
            catch
            {
                throw;
            }

            return null;

        }
        public static string[] stringTokenizers(string strCadena, char chrDelimeter)
        {
            char[] delimeterChars = { chrDelimeter };
            try
            {
                string[] arrCadena = strCadena.Split(delimeterChars);
                if (arrCadena.Length > 0)
                {
                    return arrCadena;
                }

            }
            catch
            {
                throw;
            }

            return null;

        }

       /*
        * Muestra una archivo recuperado de base datos 
        */

         public static void showFile(string fileName, byte[] fileStream, ref string sourceFile)
        {
            string sourFile = ReadBytesFile(fileName, fileStream);
            ProcessStartInfo psi = new ProcessStartInfo(sourFile);
            Process.Start(psi);
            sourceFile = sourFile;

        }

         public static void showFile(string fileName)
         {
             ProcessStartInfo psi = new ProcessStartInfo(fileName);
             Process.Start(psi);
             

         }


         public static string ReadBytesFile(string fileName, byte[] fileStream)
         {
           DirectoryInfo di = new DirectoryInfo(Path.GetTempPath() + "\\_temp");
           if (di.Exists == false)
           {
               di.Create();
           }

           FileStream fs = new FileStream(di + "\\" + fileName, FileMode.Create);
           fs.Write(fileStream, 0, System.Convert.ToInt32(fileStream.Length));
           fs.Seek(0, SeekOrigin.Begin);
           fs.Close();
       
           return di + "\\" + fileName;
         }


         public static void EnabledControl(Control root,string  strNameButton, string strUser)
         {
             
             foreach (Control ctrl in root.Controls)
             {
                 EnabledControl(ctrl, strNameButton, strUser);
                 if (ctrl is Button)
                 {
                    
                     if (((Button)ctrl).Name.Equals(strNameButton))
                     {
                        // Log.eLog(strUser,"Nombre de boton a activar:" +((Button)ctrl).Name);

                         ((Button)ctrl).Enabled = true;
                         return;
                     }


                 }
                 else if (ctrl is ToolStrip)
                 {
                     ToolStrip tstp=(ToolStrip)ctrl;
                     for(int i=0 ; i<tstp.Items.Count;i++){


                         if (tstp.Items[i].Name.Equals(strNameButton))
                         {
                            // Log.eLog(strUser, "Nombre de boton a activar:" + tstp.Items[i].Name);

                             tstp.Items[i].Enabled = true;
                             return;
                         }
                     }

                     


                 }


             }
         }

         public static string completeCeros(string strCadena )
         {
             string strTemp = "";
             if (strCadena.Length < 8) {

                 for (int i = 0; i < 8 - strCadena.Length; i++)
                 {
                     strTemp += "0";
                 }
             }


             return strTemp + strCadena.Trim();
         }


         public static string completeCeros(string strCadena,int lengthPref, int lentghField )
         {
             string strTemp = "";
             if (strCadena.Length < lentghField)
             {

                 for (int i = 0; i < lentghField - (strCadena.Length + lengthPref); i++)
                 {
                     strTemp += "0";
                 }
             }

             return strTemp + strCadena.Trim();
         }

         public static bool findCode(DataGridView dgv, string strCode, int intColumn)
         {
             for (int i = 0; i < dgv.Rows.Count; i++)
             {
                 if (dgv.Rows[i].Cells[intColumn].Value.ToString().Equals(strCode)) {

                     return true;

                 }
             }

             return false;

         }


         public static bool exportaExcel(DataGridView dgvConsulta, string strTituloMain, string strTituloSecond, ref string fileName)
         {
             bool swt = true;
             string strLeterHourWork="";
             Microsoft.Office.Interop.Excel.Application xlsApp = null;//creo una aplicación Excel
             try
             {
                 xlsApp = new Microsoft.Office.Interop.Excel.Application();//creo una aplicación Excel
                 string temp;
                 int iColumnas = 0;

                 xlsApp.DisplayAlerts = false;
                 Microsoft.Office.Interop.Excel.Worksheet xlsSheet; //creo una hoja
                 Microsoft.Office.Interop.Excel.Workbook xlsBook; //creo un libro
                 xlsApp.Visible = true; //la aplicación no es visible
                 xlsBook = xlsApp.Workbooks.Add(true);//añado el libro a la aplicación
                 xlsSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlsBook.ActiveSheet; //activo la hoja, para el libro
                 //titulo
                 xlsSheet.Cells[1, 1] = strTituloMain.ToUpper();
                 xlsSheet.Cells[2, 1] = strTituloSecond.ToUpperInvariant();
                 for (int iCol = 0; iCol < dgvConsulta.Columns.Count; iCol++)
                 {
                     if (dgvConsulta.Columns[iCol].Visible == true)
                     {
                         xlsSheet.Cells[3, iCol + 1] = dgvConsulta.Columns[iCol].HeaderText;
                         iColumnas++;
                     }
                 }

                 for (int iRow = 0; iRow < dgvConsulta.Rows.Count; iRow++)
                 {
                     for (int iCol = 0; iCol < dgvConsulta.Columns.Count; iCol++)
                     {
                         if (dgvConsulta.Columns[iCol].Visible == true)
                         {
                             if (dgvConsulta.Columns[iCol].Name.Equals("H Inicial") || dgvConsulta.Columns[iCol].Name.Equals("H Final"))
                             {
                                 temp = ((DateTime)dgvConsulta[iCol, iRow].Value).ToString("HH:mm");
                             }
                             else if (dgvConsulta.Columns[iCol].Name.Equals("Fecha"))
                             {
                                 temp = ((DateTime)dgvConsulta[iCol, iRow].Value).ToString("yyyy-MM-dd");

                             }else
                             {
                                 temp = dgvConsulta[iCol, iRow].Value.ToString();
                             }
                             xlsSheet.Cells[iRow + 4, iCol + 1] = temp;
                             if (dgvConsulta.Columns[iCol].Name.Equals("H Trab."))
                                 strLeterHourWork = columnTwoLetter(iCol);
                             //else
                             //    xlsSheet.Cells[iRow + 4, iCol + 1] = temp;


                         }
                     }
                 }
                 //Definir el rango y aplicarle un formato.

                 //Definir el rango y aplicarle un formato.
                 Log.eLog("","Letra:"+ columnTwoLetter(dgvConsulta.Columns.Count - 1) + (dgvConsulta.Rows.Count + 3));
                 Microsoft.Office.Interop.Excel.Range rango = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range("A1", columnTwoLetter(dgvConsulta.Columns.Count-1) + (dgvConsulta.Rows.Count + 3));
                
                 //Microsoft.Office.Interop.Excel.Range rango = xlsSheet.get_Range(xlsSheet.Cells[1, 1], xlsSheet.Cells[dtConsulta.Rows.Count + 3, iColumnas]);
                 //rango.EntireRow.Font.Bold = true;
                 rango.Cells.AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatList2, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                 //Microsoft.Office.Interop.Excel.Range rango = xlsSheet.get_Range(xlsSheet.Cells[1, 1], xlsSheet.Cells[dgvConsulta.Rows.Count + 3, iColumnas]);
                 //rango.Cells.AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatList1, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                 xlsSheet.Columns.AutoFit(); //Ajusta ancho de todas las columnas
                 if (!strLeterHourWork.Equals(""))
                 {
                     Microsoft.Office.Interop.Excel.Range rango2 = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLeterHourWork + "4", strLeterHourWork + (dgvConsulta.Rows.Count + 3));
                     rango2.Select();

                     xlsApp.Selection.NumberFormat = "0.00";
                 }
                 //xlsApp.Visible = true;
                 fileName = "";//Variable que almacenara el nombre del archivo.
                 while (fileName.Equals(""))
                 {
                     SaveFileDialog opdFile = new SaveFileDialog();
                     opdFile.Filter = "Archivo  Excel 2000-2003|*.xls|Archivo  Excel 2007|*.xlsx";
                     opdFile.Title = "Guardar archivo Excel.";

                     //opdFile.InitialDirectory=System.Computer.FileSystem.SpecialDirectories.MyDocuments;
                     if (opdFile.ShowDialog() == DialogResult.OK)
                     {
                         fileName = opdFile.FileName;
                         xlsSheet.SaveAs(fileName);

                     }

                     else
                     {
                         if (MessageBox.Show("¿La ruta no es válida desea continuar el guardado de archivo?", "Confirmar", MessageBoxButtons.YesNo) == DialogResult.No)
                         {
                             fileName = "xxxxxxx";
                             swt = false;
                         }
                         else
                         {
                             fileName = "";
                             
                         }
                     }


                 }



                 xlsBook.Close();
                 // xlsApp.Quit();
             }
             catch (Exception e)
             {
                 throw e;
             }
             finally
             {
                 if (xlsApp != null)
                 {
                     xlsApp.Workbooks.Close();
                     xlsApp.Quit();
                 }
                 
             }

             return swt;

         }


         public static bool exportExcelLab(DataGridView dgvConsulta, string strTituloMain, string strTituloSecond, ref string fileName)
         {
             bool swt = true;
             string strLeterHourWork = "";
             Microsoft.Office.Interop.Excel.Application xlsApp = null;//creo una aplicación Excel
             try
             {
                 xlsApp = new Microsoft.Office.Interop.Excel.Application();//creo una aplicación Excel
                 string temp;
                 int iColumnas = 0;

                 xlsApp.DisplayAlerts = false;
                 Microsoft.Office.Interop.Excel.Worksheet xlsSheet; //creo una hoja
                 Microsoft.Office.Interop.Excel.Workbook xlsBook; //creo un libro
                 xlsApp.Visible = true; //la aplicación no es visible
                 xlsBook = xlsApp.Workbooks.Add(true);//añado el libro a la aplicación
                 xlsSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlsBook.ActiveSheet; //activo la hoja, para el libro
                 //titulo
                 xlsSheet.Cells[1, 1] = strTituloMain.ToUpper();
                 xlsSheet.Cells[2, 1] = strTituloSecond.ToUpperInvariant();
                 //int intCol=0;
                 for (int iCol = 0; iCol < dgvConsulta.Columns.Count; iCol++)
                 {
                     if (dgvConsulta.Columns[iCol].Visible == true)
                     {
                         xlsSheet.Cells[3, iColumnas + 1] = dgvConsulta.Columns[iCol].HeaderText;
                         iColumnas++;
                     }
                 }

                 for (int iRow = 0; iRow < dgvConsulta.Rows.Count; iRow++)
                 {
                     int inCol=0;
                     for (int iCol = 0; iCol < dgvConsulta.Columns.Count; iCol++)
                     {
                         if (dgvConsulta.Columns[iCol].Visible == true)
                         {
                             if (dgvConsulta.Columns[iCol].Name.Equals("H Inicial") || dgvConsulta.Columns[iCol].Name.Equals("H Final"))
                             {
                                 temp = ((DateTime)dgvConsulta[iCol, iRow].Value).ToString("HH:mm");
                             }
                             else if (dgvConsulta.Columns[iCol].Name.Equals("Fecha"))
                             {
                                 temp = ((DateTime)dgvConsulta[iCol, iRow].Value).ToString("yyyy-MM-dd");

                             }
                             else
                             {
                                 temp = dgvConsulta[iCol, iRow].Value.ToString().Trim();
                             }
                             xlsSheet.Cells[iRow + 4, inCol + 1] = temp;
                             if (dgvConsulta.Columns[iCol].Name.Equals("H Trab."))
                                 strLeterHourWork = columnTwoLetter(iCol);
                             //else
                             //    xlsSheet.Cells[iRow + 4, iCol + 1] = temp;

                             inCol++;
                         }
                     }
                 }
                 //Definir el rango y aplicarle un formato.

                 //Definir el rango y aplicarle un formato.
                 Log.eLog("", "Letra:" + columnTwoLetter(dgvConsulta.Columns.Count - 1) + (dgvConsulta.Rows.Count + 3));
                 Microsoft.Office.Interop.Excel.Range rango = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range("A1", columnTwoLetter(dgvConsulta.Columns.Count - 1) + (dgvConsulta.Rows.Count + 3));
                 Util.getBordersSimple(ref xlsSheet, "A3", columnTwoLetter(dgvConsulta.Columns.Count - 3) + (dgvConsulta.Rows.Count + 3));
                 Util.setFormatNumber(ref xlsSheet, "A4", columnTwoLetter(dgvConsulta.Columns.Count - 3) + (dgvConsulta.Rows.Count + 3));
                 Util.setBold(ref xlsSheet, "A1", columnTwoLetter(dgvConsulta.Columns.Count - 3) +  3);
                 Util.getBordersSimple(ref xlsSheet, "A3", columnTwoLetter(dgvConsulta.Columns.Count - 3) + (dgvConsulta.Rows.Count + 3),Color.Blue);
                 Util.setSizeFuenteAndBold(ref xlsSheet, "A1", columnTwoLetter(dgvConsulta.Columns.Count - 3) + (dgvConsulta.Rows.Count + 3), 11, "Arial", Color.Blue,false);
                 //Microsoft.Office.Interop.Excel.Range rango = xlsSheet.get_Range(xlsSheet.Cells[1, 1], xlsSheet.Cells[dtConsulta.Rows.Count + 3, iColumnas]);
                 //rango.EntireRow.Font.Bold = true;
                // rango.Cells.AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatList2, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                 //Microsoft.Office.Interop.Excel.Range rango = xlsSheet.get_Range(xlsSheet.Cells[1, 1], xlsSheet.Cells[dgvConsulta.Rows.Count + 3, iColumnas]);
                 //rango.Cells.AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatList1, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                 xlsSheet.Columns.AutoFit(); //Ajusta ancho de todas las columnas
                 if (!strLeterHourWork.Equals(""))
                 {
                     Microsoft.Office.Interop.Excel.Range rango2 = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLeterHourWork + "4", strLeterHourWork + (dgvConsulta.Rows.Count + 3));
                     rango2.Select();

                     xlsApp.Selection.NumberFormat = "0.00";
                 }
                 //xlsApp.Visible = true;
                 fileName = "";//Variable que almacenara el nombre del archivo.
                 while (fileName.Equals(""))
                 {
                     SaveFileDialog opdFile = new SaveFileDialog();
                     opdFile.Filter = "Archivo  Excel 2000-2003|*.xls|Archivo  Excel 2007|*.xlsx";
                     opdFile.Title = "Guardar archivo Excel.";
                     opdFile.FileName = "Composito";

                     //opdFile.InitialDirectory=System.Computer.FileSystem.SpecialDirectories.MyDocuments;
                     if (opdFile.ShowDialog() == DialogResult.OK)
                     {
                         fileName = opdFile.FileName;
                         xlsSheet.SaveAs(fileName);

                     }

                     else
                     {
                         if (MessageBox.Show("¿La ruta no es válida desea continuar el guardado de archivo?", "Confirmar", MessageBoxButtons.YesNo) == DialogResult.No)
                         {
                             fileName = "xxxxxxx";
                             swt = false;
                         }
                         else
                         {
                             fileName = "";

                         }
                     }


                 }



                 xlsBook.Close();
                 // xlsApp.Quit();
             }
             catch (Exception e)
             {
                 throw e;
             }
             finally
             {
                 if (xlsApp != null)
                 {
                     xlsApp.Workbooks.Close();
                     xlsApp.Quit();
                 }

             }

             return swt;

         }



         public static bool exportaExcelAll(DataGridView dgvConsulta, string strTituloMain, string strTituloSecond, ref string fileName)
         {
             bool swt = true;
             //string strLeterHourWork = "";
             Microsoft.Office.Interop.Excel.Application xlsApp = null;//creo una aplicación Excel
             try
             {
                 xlsApp = new Microsoft.Office.Interop.Excel.Application();//creo una aplicación Excel
                 string temp;
                 int iColumnas = 0;
               

                 xlsApp.DisplayAlerts = false;
                 Microsoft.Office.Interop.Excel.Worksheet xlsSheet; //creo una hoja
                 Microsoft.Office.Interop.Excel.Workbook xlsBook; //creo un libro
                 xlsApp.Visible = false; //la aplicación no es visible
                 xlsBook = xlsApp.Workbooks.Add(true);//añado el libro a la aplicación
                 xlsSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlsBook.ActiveSheet; //activo la hoja, para el libro
                 //titulo
                 xlsSheet.Cells[1, 1] = strTituloMain.ToUpper();
                 xlsSheet.Cells[2, 1] = strTituloSecond.ToUpperInvariant();
                 for (int iCol = 0; iCol < dgvConsulta.Columns.Count; iCol++)
                 {
                     if (dgvConsulta.Columns[iCol].Visible == true)
                     {
                         xlsSheet.Cells[3, iColumnas+1] = dgvConsulta.Columns[iCol].HeaderText;
                         
                         iColumnas++;
                     }
                 }

                 for (int iRow = 0; iRow < dgvConsulta.Rows.Count; iRow++)
                 {
                     int intCol1 = 1;
                     for (int iCol = 0; iCol < dgvConsulta.Columns.Count; iCol++)
                     {
                         if (dgvConsulta.Columns[iCol].Visible == true)
                         {
                             if (dgvConsulta.Columns[iCol].Name.Equals("Fecha"))
                             {
                                 temp = ((DateTime)dgvConsulta[iCol, iRow].Value).ToString("yyyy-MM-dd");

                             }
                             else
                             {
                                 temp = dgvConsulta[iCol, iRow].Value.ToString();
                             }
                             xlsSheet.Cells[iRow + 4, intCol1] = temp;
                             //if (dgvConsulta.Columns[iCol].Name.Equals("H Trab."))
                             //    strLeterHourWork = columnTwoLetter(iCol);
                             //else
                             //    xlsSheet.Cells[iRow + 4, iCol + 1] = temp;
                             intCol1++;

                         }
                     }
                 }
                 //Definir el rango y aplicarle un formato.

                 //Definir el rango y aplicarle un formato.
                // Microsoft.Office.Interop.Excel.Range rango = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range("A1", columnTwoLetter(dgvConsulta.Columns.Count - 1) + (dgvConsulta.Rows.Count + 3));
                 Microsoft.Office.Interop.Excel.Range rango = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range("A1", columnTwoLetter(iColumnas-1) + (dgvConsulta.Rows.Count + 3));

                 //Microsoft.Office.Interop.Excel.Range rango = xlsSheet.get_Range(xlsSheet.Cells[1, 1], xlsSheet.Cells[dtConsulta.Rows.Count + 3, iColumnas]);
                 //rango.EntireRow.Font.Bold = true;
                 rango.Cells.AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatList2, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                 //Microsoft.Office.Interop.Excel.Range rango = xlsSheet.get_Range(xlsSheet.Cells[1, 1], xlsSheet.Cells[dgvConsulta.Rows.Count + 3, iColumnas]);
                 //rango.Cells.AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatList1, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                 xlsSheet.Columns.AutoFit(); //Ajusta ancho de todas las columnas

                 //Microsoft.Office.Interop.Excel.Range rango2 = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLeterHourWork + "4", strLeterHourWork + (dgvConsulta.Rows.Count + 3));
                 //rango2.Select();
                 //xlsApp.Selection.NumberFormat = "0.00";
                 //xlsApp.Visible = true;
                 fileName = "";//Variable que almacenara el nombre del archivo.
                 while (fileName.Equals(""))
                 {
                     SaveFileDialog opdFile = new SaveFileDialog();
                     opdFile.Filter = "Archivo  Excel 2000-2003|*.xls|Archivo  Excel 2007|*.xlsx";
                     opdFile.Title = "Guardar archivo Excel.";

                     //opdFile.InitialDirectory=System.Computer.FileSystem.SpecialDirectories.MyDocuments;
                     if (opdFile.ShowDialog() == DialogResult.OK)
                     {
                         fileName = opdFile.FileName;
                         xlsSheet.SaveAs(fileName);

                     }

                     else
                     {
                         if (MessageBox.Show("¿La ruta no es válida desea continuar el guardado de archivo?", "Confirmar", MessageBoxButtons.YesNo) == DialogResult.No)
                         {
                             fileName = "xxxxxxx";
                             swt = false;
                         }
                         else
                         {
                             fileName = "";

                         }
                     }


                 }



                 xlsBook.Close();
                 // xlsApp.Quit();
             }
             catch (Exception e)
             {
                 throw e;
             }
             finally
             {
                 if (xlsApp != null)
                 {
                     xlsApp.Workbooks.Close();
                     xlsApp.Quit();
                 }

             }

             return swt;

         }




         public static bool exportDataGridExcel(DataTable dtConsulta, string strTituloMain, string strTituloSecond,string strHours, string strTotalAttendes, ref string fileName)
         {
             bool swt = true ;
             Microsoft.Office.Interop.Excel.Application xlsApp = null;//creo una aplicación Excel

             try
             {
                 xlsApp = new Microsoft.Office.Interop.Excel.Application();//creo una aplicación Excel
                 string temp;
                 int iColumnas = 0;

                 xlsApp.DisplayAlerts = false;
                 Microsoft.Office.Interop.Excel.Worksheet xlsSheet; //creo una hoja
                 Microsoft.Office.Interop.Excel.Workbook xlsBook; //creo un libro
                 xlsApp.Visible = false; //la aplicación no es visible
                 xlsBook = xlsApp.Workbooks.Add(true);//añado el libro a la aplicación
                 xlsSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlsBook.ActiveSheet; //activo la hoja, para el libro
                 //titulo
                 xlsSheet.Cells[1, 1] = strTituloMain.ToUpper();
                 xlsSheet.Cells[2, 1] = strTituloSecond.ToUpperInvariant();
                 //Insertamos el total de horas capacitadas:
                 xlsSheet.Cells[2, 3] = "Total Horas de Capacitación:";
                 xlsSheet.Cells[2, 4] = strHours;
                 if (!strTotalAttendes.Trim().Equals(""))
                 {
                     xlsSheet.Cells[2, 5] = "Total Asistentes:";
                     xlsSheet.Cells[2, 6] = strTotalAttendes;
                 }
                 for (int iCol = 0; iCol < dtConsulta.Columns.Count; iCol++)
                 {
                     
                         xlsSheet.Cells[3, iCol + 1] = dtConsulta.Columns[iCol].ToString();
                         iColumnas++;
                     
                 }

                 for (int iRow = 0; iRow < dtConsulta.Rows.Count; iRow++)
                 {
                     for (int iCol = 0; iCol < dtConsulta.Columns.Count; iCol++)
                     {
                         if (dtConsulta.Columns[iCol].ColumnName.Equals("Hora Inicio") || dtConsulta.Columns[iCol].ColumnName.Equals("Hora Término"))
                         {
                             //try{
                             //    if(dtConsulta.Columns[iCol].ColumnName.Equals("Fecha Registro"))
                             //       xlsSheet.Cells[iRow + 4, iCol + 1] =((DateTime) dtConsulta.Rows[iRow][iCol]).ToString("dd-MM-yyyy");
                             //    else
                            // Log.eLog(null, "ingreso Hora");
                             xlsSheet.Cells[iRow + 4, iCol + 1] = ((DateTime)dtConsulta.Rows[iRow][iCol]).ToString("HH:mm");
                             
                         }

                         else if (dtConsulta.Columns[iCol].ColumnName.Equals("Fecha Registro"))
                         {
                             //Log.eLog(null, "ingreso fecha");
                             xlsSheet.Cells[iRow + 4, iCol + 1] = ((DateTime)dtConsulta.Rows[iRow][iCol]).ToString("MM/dd/yyyy");
                         }
                         else if (dtConsulta.Columns[iCol].ColumnName.Equals("dni")) {
                             Microsoft.Office.Interop.Excel.Range rangoCell = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range((columnTwoLetter(iCol) + (iRow + 4)), (columnTwoLetter(iCol) + (iRow + 4)));
                             rangoCell.Select();
                             xlsApp.Selection.NumberFormat = "@";
                             xlsSheet.Cells[iRow + 4, iCol + 1] = dtConsulta.Rows[iRow][iCol].ToString();
                         
                         }
                         else

                             xlsSheet.Cells[iRow + 4, iCol + 1] = dtConsulta.Rows[iRow][iCol].ToString();
                         
                            
                       
                         
                     }
                 }

                 

                 //Definir el rango y aplicarle un formato.

                 //Definir el rango y aplicarle un formato
                 //Log.eLog(null, "Celda:"+xlsSheet.Columns[1].Name);
                 Microsoft.Office.Interop.Excel.Range rango = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range("A1", columnTwoLetter(dtConsulta.Columns.Count-1) + (dtConsulta.Rows.Count + 3));
                 //Microsoft.Office.Interop.Excel.Range rango = xlsSheet.get_Range(xlsSheet.Cells[1, 1], xlsSheet.Cells[dtConsulta.Rows.Count + 3, iColumnas]);
                 //rango.EntireRow.Font.Bold = true;
                 rango.Cells.AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatList2, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                 xlsSheet.Columns.AutoFit(); //Ajusta ancho de todas las columnas
                 //xlsApp.Visible = true;
                 fileName = "";//Variable que almacenara el nombre del archivo.
                 while (fileName.Equals(""))
                 {
                     SaveFileDialog opdFile = new SaveFileDialog();
                     opdFile.Filter = "Archivo  Excel 2000-2003|*.xls|Archivo  Excel 2007|*.xlsx";
                     opdFile.Title = "Guardar archivo Excel.";

                     //opdFile.InitialDirectory=System.Computer.FileSystem.SpecialDirectories.MyDocuments;
                     if (opdFile.ShowDialog() == DialogResult.OK)
                     {
                         fileName = opdFile.FileName;
                         xlsSheet.SaveAs(fileName);

                     }

                     else
                     {
                         if (MessageBox.Show("¿La ruta no es válida desea continuar el guardado de archivo?", "Confirmar", MessageBoxButtons.YesNo) == DialogResult.No)
                         {
                             fileName = "xxxxxxx";
                             swt = false;
                         }
                         else
                         {
                             fileName = "";
                             
                         }
                     }


                 }


                 xlsBook.Close();
                
                 // xlsApp.Quit();
             }
             catch (Exception e)
             {
                 throw e;
             }
             finally
             {
                 if (xlsApp != null)
                 {  
                     xlsApp.Workbooks.Close();
                     xlsApp.Quit();
                 }

             }

             return swt;

         }

         public static bool exportDataGridExcel(DataGridView dgvConsulta, string strTituloMain, string strTituloSecond, string strHours, string strTotalAttendes, ref string fileName)
         {
             bool swt = true;
             Microsoft.Office.Interop.Excel.Application xlsApp = null;//creo una aplicación Excel

             try
             {
                 xlsApp = new Microsoft.Office.Interop.Excel.Application();//creo una aplicación Excel
                 
                 int iColumnas = 0;

                 xlsApp.DisplayAlerts = false;
                 Microsoft.Office.Interop.Excel.Worksheet xlsSheet; //creo una hoja
                 Microsoft.Office.Interop.Excel.Workbook xlsBook; //creo un libro
                 xlsApp.Visible = true; //la aplicación no es visible
                 xlsBook = xlsApp.Workbooks.Add(true);//añado el libro a la aplicación
                 xlsSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlsBook.ActiveSheet; //activo la hoja, para el libro
                 //titulo
                 xlsSheet.Cells[1, 1] = strTituloMain.ToUpper();
                 xlsSheet.Cells[2, 1] = strTituloSecond.ToUpperInvariant();
                 //Insertamos el total de horas capacitadas:
                 xlsSheet.Cells[2, 3] = "Total Horas de Capacitación:";
                 Microsoft.Office.Interop.Excel.Range rangoHor = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range("D2");
                 rangoHor.Select();
                 xlsApp.Selection.NumberFormat = "@";
                 xlsSheet.Cells[2, 4] = strHours;
                 if (!strTotalAttendes.Trim().Equals(""))
                 {
                     xlsSheet.Cells[2, 5] = "Total Asistentes:";
                     xlsSheet.Cells[2, 6] = strTotalAttendes;
                 }
                 for (int iCol = 0; iCol < dgvConsulta.Columns.Count; iCol++)
                 {

                     xlsSheet.Cells[3, iCol + 1] = dgvConsulta.Columns[iCol].HeaderText.ToString();
                     iColumnas++;

                 }

                 for (int iRow = 0; iRow < dgvConsulta.Rows.Count; iRow++)
                 {
                     for (int iCol = 0; iCol < dgvConsulta.Columns.Count; iCol++)
                     {
                         //if (iCol >4)
                         //{
                             //try{
                             //    if(dtConsulta.Columns[iCol].ColumnName.Equals("Fecha Registro"))
                             //       xlsSheet.Cells[iRow + 4, iCol + 1] =((DateTime) dtConsulta.Rows[iRow][iCol]).ToString("dd-MM-yyyy");
                             //    else

                             // Log.eLog(null, "ingreso Hora");
                             Microsoft.Office.Interop.Excel.Range rangoCell = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range((columnTwoLetter(iCol + 1) + (iRow + 4)), (columnTwoLetter(iCol + 1) +( iRow + 4)));
                             rangoCell.Select();    
                             xlsApp.Selection.NumberFormat = "@";
                             xlsSheet.Cells[iRow + 4, iCol + 1] = dgvConsulta.Rows[iRow].Cells[iCol].Value;
                             

                         //}
                      
                         //else

                         //   xlsSheet.Cells[iRow + 4, iCol + 1] = dgvConsulta.Rows[iRow].Cells[iCol].Value;




                     }
                 }



                 //Definir el rango y aplicarle un formato.

                 //Definir el rango y aplicarle un formato
                 //Log.eLog(null, "Celda:"+xlsSheet.Columns[1].Name);
                 Microsoft.Office.Interop.Excel.Range rango = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range("A1", columnTwoLetter(dgvConsulta.Columns.Count - 1) + (dgvConsulta.Rows.Count + 3));
                 //Microsoft.Office.Interop.Excel.Range rango = xlsSheet.get_Range(xlsSheet.Cells[1, 1], xlsSheet.Cells[dtConsulta.Rows.Count + 3, iColumnas]);
                 //rango.EntireRow.Font.Bold = true;
                 
                 rango.Cells.AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatList2, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                 xlsSheet.Columns.AutoFit(); //Ajusta ancho de todas las columnas
                 rango.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                 //xlsApp.Visible = true;
                 fileName = "";//Variable que almacenara el nombre del archivo.
                 while (fileName.Equals(""))
                 {
                     SaveFileDialog opdFile = new SaveFileDialog();
                     opdFile.Filter = "Archivo  Excel 2000-2003|*.xls|Archivo  Excel 2007|*.xlsx";
                     opdFile.Title = "Guardar archivo Excel.";

                     //opdFile.InitialDirectory=System.Computer.FileSystem.SpecialDirectories.MyDocuments;
                     if (opdFile.ShowDialog() == DialogResult.OK)
                     {
                         fileName = opdFile.FileName;
                         xlsSheet.SaveAs(fileName);

                     }

                     else
                     {
                         if (MessageBox.Show("¿La ruta no es válida desea continuar el guardado de archivo?", "Confirmar", MessageBoxButtons.YesNo) == DialogResult.No)
                         {
                             fileName = "xxxxxxx";
                             swt = false;
                         }
                         else
                         {
                             fileName = "";

                         }
                     }


                 }


                 xlsBook.Close();

                 // xlsApp.Quit();
             }
             catch (Exception e)
             {
                 throw e;
             }
             finally
             {
                 if (xlsApp != null)
                 {
                     xlsApp.Workbooks.Close();
                     xlsApp.Quit();
                 }

             }

             return swt;

         }


         public static bool exportDataTableExcelWithColour(DataTable dtConsulta, string strTituloMain, string strTituloSecond, ref string fileName)
         {
             bool swt = true;
             Microsoft.Office.Interop.Excel.Application xlsApp = null;//creo una aplicación Excel
           
             

             try
             {
                 xlsApp = new Microsoft.Office.Interop.Excel.Application();//creo una aplicación Excel
                 string temp;
                 int iColumnas = 0;

                 xlsApp.DisplayAlerts = false;
                 Microsoft.Office.Interop.Excel.Worksheet xlsSheet; //creo una hoja
                 Microsoft.Office.Interop.Excel.Workbook xlsBook; //creo un libro

                 xlsApp.Visible = false; //la aplicación no es visible
                 xlsBook = xlsApp.Workbooks.Add(true);//añado el libro a la aplicación
                 xlsSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlsBook.ActiveSheet; //activo la hoja, para el libro
                 //titulo
                 xlsSheet.Cells[1, 1] = strTituloMain.ToUpper();
                 xlsSheet.Cells[2, 1] = strTituloSecond.ToUpperInvariant();
                 //Insertamos el total de horas capacitadas:
                 //xlsSheet.Cells[2, 3] = "Total Horas de Capacitación:";
                 //xlsSheet.Cells[2, 4] = strHours;
                 for (int iCol = 0; iCol < dtConsulta.Columns.Count; iCol++)
                 {

                     xlsSheet.Cells[3, iCol + 1] = dtConsulta.Columns[iCol].ToString();
                     iColumnas++;

                 }

                 for (int iRow = 0; iRow < dtConsulta.Rows.Count; iRow++)
                 {
                     for (int iCol = 0; iCol < dtConsulta.Columns.Count; iCol++)
                     {
                         if (dtConsulta.Columns[iCol].ColumnName.Equals("Hora Inicio") || dtConsulta.Columns[iCol].ColumnName.Equals("Hora Término"))
                             //try{
                             //    if(dtConsulta.Columns[iCol].ColumnName.Equals("Fecha Registro"))
                             //       xlsSheet.Cells[iRow + 4, iCol + 1] =((DateTime) dtConsulta.Rows[iRow][iCol]).ToString("dd-MM-yyyy");
                             //    else
                             xlsSheet.Cells[iRow + 4, iCol + 1] = ((DateTime)dtConsulta.Rows[iRow][iCol]).ToString("HH:mm");

                         else
                             xlsSheet.Cells[iRow + 4, iCol + 1] = dtConsulta.Rows[iRow][iCol].ToString();




                     }
                     if (dtConsulta.Rows[iRow][0].ToString().Equals(""))
                     {

                         Log.eLog(null, "valor: A" + (iRow+4) + ":" + columnTwoLetter(dtConsulta.Columns.Count - 1) + (iRow+4));
                         Microsoft.Office.Interop.Excel.Range rango = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range("A" + (iRow+4), columnTwoLetter(dtConsulta.Columns.Count - 1) + (iRow+4));
                         //Microsoft.Office.Interop.Excel.Range rango = xlsSheet.get_Range(xlsSheet.Cells[1, 1], xlsSheet.Cells[dtConsulta.Rows.Count + 3, iColumnas]);
                         //rango.EntireRow.Font.Bold = true;
                         rango.Interior.Color = Color.FromArgb(196, 230, 234);
                         
                         
                         

                     }
                 }



                 //Definir el rango y aplicarle un formato.

                 //Definir el rango y aplicarle un formato
                 //Log.eLog(null, "Celda:"+xlsSheet.Columns[1].Name);
                 Microsoft.Office.Interop.Excel.Range rango2 = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range("A1", columnTwoLetter(dtConsulta.Columns.Count - 1) + (dtConsulta.Rows.Count + 3));
                 //Microsoft.Office.Interop.Excel.Range rango = xlsSheet.get_Range(xlsSheet.Cells[1, 1], xlsSheet.Cells[dtConsulta.Rows.Count + 3, iColumnas]);
                 //rango.EntireRow.Font.Bold = true;
                 rango2.Select();
                 rango2.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
                 rango2.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
                 rango2.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                 rango2.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                 rango2.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                 rango2.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                 rango2.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                 rango2.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

                // rango.Interior.ColorIndex = 9;//Color.FromArgb(200, 160, 35);
                // rango.Cells.AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                 xlsSheet.Columns.AutoFit(); //Ajusta ancho de todas las columnas
                 //xlsApp.Visible = true;
                 fileName = "";//Variable que almacenara el nombre del archivo.
                 while (fileName.Equals(""))
                 {
                     SaveFileDialog opdFile = new SaveFileDialog();
                     opdFile.Filter = "Archivo  Excel 2000-2003|*.xls|Archivo  Excel 2007|*.xlsx";
                     opdFile.Title = "Guardar archivo Excel.";

                     //opdFile.InitialDirectory=System.Computer.FileSystem.SpecialDirectories.MyDocuments;
                     if (opdFile.ShowDialog() == DialogResult.OK)
                     {
                         fileName = opdFile.FileName;
                         xlsSheet.SaveAs(fileName);

                     }

                     else
                     {
                         if (MessageBox.Show("¿La ruta no es válida desea continuar el guardado de archivo?", "Confirmar", MessageBoxButtons.YesNo) == DialogResult.No)
                         {
                             fileName = "xxxxxxx";
                             swt = false;
                         }
                         else
                         {
                             fileName = "";

                         }
                     }


                 }


                 xlsBook.Close();

                 // xlsApp.Quit();
             }
             catch (Exception e)
             {
                 throw e;
             }
             finally
             {
                 if (xlsApp != null)
                 {
                     xlsApp.Workbooks.Close();
                     xlsApp.Quit();
                 }

             }

             return swt;

         }


         public static bool exportaExcelProgram(DataGridView dgvConsulta, string strTituloMain, string strTituloSecond, ref string fileName)
         {
             bool swt = true;
             //string strLeterHourWork = "";
             Microsoft.Office.Interop.Excel.Application xlsApp = null;//creo una aplicación Excel
             try
             {
                 xlsApp = new Microsoft.Office.Interop.Excel.Application();//creo una aplicación Excel
                 string temp;
                 int iColumnas = 0;


                 xlsApp.DisplayAlerts = false;
                 Microsoft.Office.Interop.Excel.Worksheet xlsSheet; //creo una hoja
                 Microsoft.Office.Interop.Excel.Workbook xlsBook; //creo un libro
                 xlsApp.Visible = true; //la aplicación no es visible
                 xlsBook = xlsApp.Workbooks.Add(true);//añado el libro a la aplicación
                 xlsSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlsBook.ActiveSheet; //activo la hoja, para el libro
                 //titulo
                 xlsSheet.Cells[1, 1] = strTituloMain.ToUpper();
                 xlsSheet.Cells[2, 1] = strTituloSecond.ToUpperInvariant();
                 for (int iCol = 0; iCol < dgvConsulta.Columns.Count; iCol++)
                 {
                     if (dgvConsulta.Columns[iCol].Visible == true)
                     {
                         xlsSheet.Cells[3, iColumnas + 1] = dgvConsulta.Columns[iCol].HeaderText;

                         iColumnas++;
                     }
                 }

                 for (int iRow = 0; iRow < dgvConsulta.Rows.Count; iRow++)
                 {
                     int intCol1 = 1;
                     for (int iCol = 0; iCol < dgvConsulta.Columns.Count; iCol++)
                     {
                         if (dgvConsulta.Columns[iCol].Visible == true)
                         {
                             if (dgvConsulta.Columns[iCol].Name.Equals("Fecha"))
                             {
                                 temp = ((DateTime)dgvConsulta[iCol, iRow].Value).ToString("yyyy-MM-dd");

                             }
                             else
                             {
                                 temp = dgvConsulta[iCol, iRow].Value != null?dgvConsulta[iCol, iRow].Value.ToString():null;
                                   
                             }
                             xlsSheet.Cells[iRow + 4, intCol1] = temp;
                             if (Util.isnumeric(temp) && temp != null && iCol < dgvConsulta.Columns.Count - 1)
                             {
                                 //xlsSheet.Range[Util.columnTwoLetter(intCol1) + (iRow + 4)]..Interior.Color = Color.Blue;
                                 Microsoft.Office.Interop.Excel.Range rangoRowCell =
                                     (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(Util.columnTwoLetter(intCol1-1) + (iRow + 4), Util.columnTwoLetter(intCol1-1) + (iRow + 4));
                                 rangoRowCell.Interior.Color = Color.Blue;
                                 rangoRowCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                             }
                             intCol1++;

                         }
                     }
                    

                         Microsoft.Office.Interop.Excel.Range rangoRow = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range("A" + (iRow + 4), columnTwoLetter(iColumnas - 1) + (iRow + 4));
                         //rangoRow.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                         //rangoRow.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                         Log.eLog("", "Color:" + dgvConsulta.Rows[iRow].DefaultCellStyle.BackColor.Name);
                         if (dgvConsulta.Rows[iRow].DefaultCellStyle.BackColor.Name.Equals("PowderBlue"))
                             rangoRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.PowderBlue);
                    
                    // xlsApp.Selection.Interior.Pattern = Microsoft.Office.Interop.Excel.;
                 }
                 //Definir el rango y aplicarle un formato.

                 //Definir el rango y aplicarle un formato.
                 // Microsoft.Office.Interop.Excel.Range rango = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range("A1", columnTwoLetter(dgvConsulta.Columns.Count - 1) + (dgvConsulta.Rows.Count + 3));
                 Microsoft.Office.Interop.Excel.Range rango = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range("A3", columnTwoLetter(iColumnas - 1) + (dgvConsulta.Rows.Count + 3));

                 rango.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                 //Microsoft.Office.Interop.Excel.Range rango = xlsSheet.get_Range(xlsSheet.Cells[1, 1], xlsSheet.Cells[dtConsulta.Rows.Count + 3, iColumnas]);
                 //rango.EntireRow.Font.Bold = true;
                 //rango.Cells.AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatList2, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                 //Microsoft.Office.Interop.Excel.Range rango = xlsSheet.get_Range(xlsSheet.Cells[1, 1], xlsSheet.Cells[dgvConsulta.Rows.Count + 3, iColumnas]);
                 //rango.Cells.AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatList1, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                 //xlsSheet.Columns.AutoFit(); ; //Ajusta ancho de todas las columnas
                 xlsSheet.Range["A1"].EntireColumn.ColumnWidth = 30;
                 xlsSheet.Range["B1"].EntireColumn.ColumnWidth = 30;
                 xlsSheet.Range[ columnTwoLetter(iColumnas - 1)+"1"].EntireColumn.ColumnWidth = 15;
                 
                // xlsSheet.Columns[1].AutoFit(); //Ajusta ancho de todas las columnas

                 //Microsoft.Office.Interop.Excel.Range rango2 = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLeterHourWork + "4", strLeterHourWork + (dgvConsulta.Rows.Count + 3));
                 //rango2.Select();
                 //xlsApp.Selection.NumberFormat = "0.00";
                 //xlsApp.Visible = true;

                 //Combinar  columnas 
                 for (int iRow = 0; iRow < dgvConsulta.Rows.Count; iRow++)
                 {
                     //Log.eLog("", dgvConsulta.Rows[iRow].Cells["item"].Value+"");
                     if ((iRow + 1) < dgvConsulta.Rows.Count && dgvConsulta.Rows[iRow].Cells["item"].Value != null)
                     {
                         
                         getCombineCell(ref xlsSheet, "A",iRow+4);
                         getCombineCell(ref xlsSheet, "B", iRow + 4);
                         getCombineCell(ref xlsSheet, Util.columnTwoLetter(dgvConsulta.Columns.Count-1), iRow + 4);
                         iRow++;
                     }
                     
                 
                 
                 }


                 fileName = "";//Variable que almacenara el nombre del archivo.
                 while (fileName.Equals(""))
                 {
                     SaveFileDialog opdFile = new SaveFileDialog();
                     opdFile.Filter = "Archivo  Excel 2000-2003|*.xls|Archivo  Excel 2007|*.xlsx";
                     opdFile.Title = "Guardar archivo Excel.";

                     //opdFile.InitialDirectory=System.Computer.FileSystem.SpecialDirectories.MyDocuments;
                     if (opdFile.ShowDialog() == DialogResult.OK)
                     {
                         fileName = opdFile.FileName;
                         xlsSheet.SaveAs(fileName);

                     }

                     else
                     {
                         if (MessageBox.Show("¿La ruta no es válida desea continuar el guardado de archivo?", "Confirmar", MessageBoxButtons.YesNo) == DialogResult.No)
                         {
                             fileName = "xxxxxxx";
                             swt = false;
                         }
                         else
                         {
                             fileName = "";

                         }
                     }


                 }



                 xlsBook.Close();
                 // xlsApp.Quit();
             }
             catch (Exception e)
             {
                 throw e;
             }
             finally
             {
                 if (xlsApp != null)
                 {
                     xlsApp.Workbooks.Close();
                     xlsApp.Quit();
                 }

             }

             return swt;

         }



         public static bool exportaExcelResidue(string strTituloMain, string strTituloSecond, ref string fileName,
             DataGridView[] dgvs, TabControl tabControl, DataGridView dgvDataGraf)
         {
             bool swt = true;
             //string strLeterHourWork = "";
             Microsoft.Office.Interop.Excel.Application xlsApp = null;//creo una aplicación Excel
             
             
             try
             {
                 xlsApp = new Microsoft.Office.Interop.Excel.Application();//creo una aplicación Excel
                 string temp="";
                 int iColumnas = 0;

                    
                 xlsApp.DisplayAlerts = false;
                 Microsoft.Office.Interop.Excel.Workbook xlsBook; //creo un libro
                 //Microsoft.Office.Interop.Excel.Worksheet[] xlsSheet; //creo una hoja
                
                 Microsoft.Office.Interop.Excel.Sheets sheets = null;
                 //xlsSheet = new Microsoft.Office.Interop.Excel.Worksheet[numTab];
                 xlsApp.Visible = true; //la aplicación no es visible
                 
                 xlsBook = xlsApp.Workbooks.Add(true);//añado el libro a la aplicación
                // oChart = (Microsoft.Office.Interop.Excel._Chart)xlsBook.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                 //Realizamos el grafico

                 Microsoft.Office.Interop.Excel.Worksheet xlsSheetGraf = (Microsoft.Office.Interop.Excel.Worksheet)xlsBook.ActiveSheet; //activo la hoja, para el libro
                 xlsSheetGraf.Name = "GRÁFICO";
                 for (int iCol = 0; iCol < dgvDataGraf.Columns.Count; iCol++)
                 {
                     if (dgvDataGraf.Columns[iCol].Visible == true)
                     {
                         xlsSheetGraf.Cells[3, iColumnas + 1] = dgvDataGraf.Columns[iCol].HeaderText;

                         iColumnas++;
                     }
                 }


                 for (int iRow = 0; iRow < dgvDataGraf.Rows.Count; iRow++)
                 {
                     int intCol1 = 1;
                     for (int iCol = 0; iCol < dgvDataGraf.Columns.Count; iCol++)
                     {
                         if (dgvDataGraf.Columns[iCol].Visible == true)
                         {
                             if (dgvDataGraf.Columns[iCol].Name.Equals("Fecha"))
                             {
                                 temp = ((DateTime)dgvDataGraf[iCol, iRow].Value).ToString("yyyy-MM-dd");

                             }
                             else
                             {
                                 //if(dgvDataGraf.Columns[iCol].Name.Equals("Total") || dgvDataGraf.Columns[iCol].Name.Equals("Generacio%"))
                                 temp = dgvDataGraf[iCol, iRow].Value != null ? dgvDataGraf[iCol, iRow].Value.ToString() : null;

                             }
                             if (dgvDataGraf.Columns[iCol].HeaderText.Equals("Generación(%)"))
                                 xlsSheetGraf.Cells[iRow + 4, intCol1] =Util.isDouble(temp)?(Convert.ToDouble(temp)*100)+"%":temp;
                             else
                                 xlsSheetGraf.Cells[iRow + 4, intCol1] = temp;
                             //if (Util.isnumeric(temp) && temp != null && iCol < dgvDataGraf.Columns.Count - 1)
                             //{
                             //    //xlsSheet.Range[Util.columnTwoLetter(intCol1) + (iRow + 4)]..Interior.Color = Color.Blue;
                             //    Microsoft.Office.Interop.Excel.Range rangoRowCell =
                             //        (Microsoft.Office.Interop.Excel.Range)xlsSheetGraf.get_Range(Util.columnTwoLetter(intCol1 - 1) + (iRow + 4), Util.columnTwoLetter(intCol1 - 1) + (iRow + 4));
                             //    rangoRowCell.Interior.Color = Color.Blue;
                             //    rangoRowCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                             //}
                             intCol1++;

                         }
                     }


                     Microsoft.Office.Interop.Excel.Range rangoRowG = (Microsoft.Office.Interop.Excel.Range)xlsSheetGraf.get_Range("A" + (iRow + 4), columnTwoLetter(iColumnas - 3) + (iRow + 4));
                     //rangoRow.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                     //rangoRow.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                     //Log.eLog("", "Color:" + dgvDataGraf.Rows[iRow].Cells["description"].Style.BackColor.Name);
                     if (!dgvDataGraf.Rows[iRow].Cells["description"].Style.BackColor.Name.Equals("0"))
                         rangoRowG.Interior.Color = dgvDataGraf.Rows[iRow].Cells["description"].Style.BackColor;//System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.PowderBlue);
                     if (dgvDataGraf.Rows[iRow].Cells["description"].Style.BackColor.Name.Equals("ff000000"))
                         rangoRowG.Cells.Font.Color = Color.White;

                     // xlsApp.Selection.Interior.Pattern = Microsoft.Office.Interop.Excel.;
                 }

                 Microsoft.Office.Interop.Excel.Range rangoG = (Microsoft.Office.Interop.Excel.Range)xlsSheetGraf.get_Range("A3", columnTwoLetter(iColumnas - 1) + (dgvDataGraf.Rows.Count + 3));

                 rangoG.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                 xlsSheetGraf.Columns.AutoFit();


                 //Cargamos datos para el grafico
                 graphAllTypeResidue(ref xlsApp,ref xlsBook, ref xlsSheetGraf,dgvDataGraf);



                 for (int i = tabControl.TabPages.Count-1; i >= 0; i--)
                 {

                     if (!(dgvs[i] != null)) continue;
                    
                     Microsoft.Office.Interop.Excel.Worksheet xlsSheet = null;
                     sheets = xlsBook.Sheets as Microsoft.Office.Interop.Excel.Sheets; //Inicializar hoja

                     xlsSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
                     //titulo
                     xlsSheet.Name = tabControl.TabPages[i].Text;
                     xlsSheet.Cells[1, 1] = strTituloMain.ToUpper();
                     xlsSheet.Cells[2, 1] = strTituloSecond.ToUpperInvariant();
                     iColumnas = 0;
                     for (int iCol = 0; iCol < dgvs[i].Columns.Count; iCol++)
                     {
                         if (dgvs[i].Columns[iCol].Visible == true)
                         {
                             xlsSheet.Cells[3, iColumnas + 1] = dgvs[i].Columns[iCol].HeaderText;

                             iColumnas++;
                         }
                     }

                     for (int iRow = 0; iRow < dgvs[i].Rows.Count; iRow++)
                     {
                         int intCol1 = 1;
                         for (int iCol = 0; iCol < dgvs[i].Columns.Count; iCol++)
                         {
                             if (dgvs[i].Columns[iCol].Visible == true)
                             {
                                 if (dgvs[i].Columns[iCol].Name.Equals("Fecha"))
                                 {
                                     temp = ((DateTime)dgvs[i][iCol, iRow].Value).ToString("yyyy-MM-dd");

                                 }
                                 else
                                 {
                                     temp = dgvs[i][iCol, iRow].Value != null ? dgvs[i][iCol, iRow].Value.ToString() : null;

                                 }
                                 xlsSheet.Cells[iRow + 4, intCol1] = temp;
                                 if (Util.isnumeric(temp) && temp != null && iCol < dgvs[i].Columns.Count - 1)
                                 {
                                     //xlsSheet.Range[Util.columnTwoLetter(intCol1) + (iRow + 4)]..Interior.Color = Color.Blue;
                                     Microsoft.Office.Interop.Excel.Range rangoRowCell =
                                         (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(Util.columnTwoLetter(intCol1 - 1) + (iRow + 4), Util.columnTwoLetter(intCol1 - 1) + (iRow + 4));
                                     rangoRowCell.Interior.Color = Color.Blue;
                                     rangoRowCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                                 }
                                 intCol1++;

                             }
                         }


                         Microsoft.Office.Interop.Excel.Range rangoRow = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range("A" + (iRow + 4), columnTwoLetter(iColumnas - 1) + (iRow + 4));
                         //rangoRow.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                         //rangoRow.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                         //Log.eLog("", "Color:" + dgvs[i].Rows[iRow].DefaultCellStyle.BackColor.Name);
                         if (!dgvs[i].Rows[iRow].DefaultCellStyle.BackColor.Name.Equals("0"))
                             rangoRow.Interior.Color = dgvs[i].Rows[iRow].DefaultCellStyle.BackColor;//System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.PowderBlue);

                         // xlsApp.Selection.Interior.Pattern = Microsoft.Office.Interop.Excel.;
                     }
                     //Definir el rango y aplicarle un formato.

                     //Definir el rango y aplicarle un formato.
                     // Microsoft.Office.Interop.Excel.Range rango = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range("A1", columnTwoLetter(dgvConsulta.Columns.Count - 1) + (dgvConsulta.Rows.Count + 3));
                     Microsoft.Office.Interop.Excel.Range rango = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range("A3", columnTwoLetter(iColumnas - 1) + (dgvs[i].Rows.Count + 3));

                     rango.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                     //Microsoft.Office.Interop.Excel.Range rango = xlsSheet.get_Range(xlsSheet.Cells[1, 1], xlsSheet.Cells[dtConsulta.Rows.Count + 3, iColumnas]);
                     //rango.EntireRow.Font.Bold = true;
                     //rango.Cells.AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatList2, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                     //Microsoft.Office.Interop.Excel.Range rango = xlsSheet.get_Range(xlsSheet.Cells[1, 1], xlsSheet.Cells[dgvConsulta.Rows.Count + 3, iColumnas]);
                     //rango.Cells.AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatList1, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                     //xlsSheet.Columns.AutoFit(); ; //Ajusta ancho de todas las columnas
                     xlsSheet.Range["A1"].EntireColumn.ColumnWidth = 30;
                     xlsSheet.Range["B1"].EntireColumn.ColumnWidth = 30;
                     xlsSheet.Range[columnTwoLetter(iColumnas - 1) + "1"].EntireColumn.ColumnWidth = 15;

                     // xlsSheet.Columns[1].AutoFit(); //Ajusta ancho de todas las columnas

                     //Microsoft.Office.Interop.Excel.Range rango2 = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLeterHourWork + "4", strLeterHourWork + (dgvConsulta.Rows.Count + 3));
                     //rango2.Select();
                     //xlsApp.Selection.NumberFormat = "0.00";
                     //xlsApp.Visible = true;

                     //Combinar  columnas 
                     //for (int iRow = 0; iRow < dgvs[i].Rows.Count; iRow++)
                     //{
                     //    //Log.eLog("", dgvConsulta.Rows[iRow].Cells["item"].Value+"");
                     //    if ((iRow + 1) < dgvs[i].Rows.Count && dgvs[i].Rows[iRow].Cells["item"].Value != null)
                     //    {

                     //        getCombineCell(ref xlsSheet, "A", iRow + 4);
                     //        getCombineCell(ref xlsSheet, "B", iRow + 4);
                     //        getCombineCell(ref xlsSheet, Util.columnTwoLetter(dgvs[i].Columns.Count - 1), iRow + 4);
                     //        iRow++;
                     //    }



                     //}
                 }

                 fileName = "";//Variable que almacenara el nombre del archivo.
                 while (fileName.Equals(""))
                 {
                     SaveFileDialog opdFile = new SaveFileDialog();
                     opdFile.Filter = "Archivo  Excel 2000-2003|*.xls|Archivo  Excel 2007|*.xlsx";
                     opdFile.Title = "Guardar archivo Excel.";

                     //opdFile.InitialDirectory=System.Computer.FileSystem.SpecialDirectories.MyDocuments;
                     if (opdFile.ShowDialog() == DialogResult.OK)
                     {
                         fileName = opdFile.FileName;
                         //xlsSheet.SaveAs(fileName);
                         xlsBook.SaveAs(fileName);
                     }

                     else
                     {
                         if (MessageBox.Show("¿La ruta no es válida desea continuar el guardado de archivo?", "Confirmar", MessageBoxButtons.YesNo) == DialogResult.No)
                         {
                             fileName = "xxxxxxx";
                             swt = false;
                         }
                         else
                         {
                             fileName = "";

                         }
                     }


                 }



                 xlsBook.Close();
                 // xlsApp.Quit();
             }
             catch (Exception e)
             {
                 throw e;
             }
             finally
             {
                 if (xlsApp != null)
                 {
                     xlsApp.Workbooks.Close();
                     xlsApp.Quit();
                 }

             }

             return swt;

         }



         public static bool exportaExcelPointMonitoring(string strTituloMain, string strTituloSecond, ref string fileName,
                 DataGridView dgvDataGraf, double dblLimitPer, string strCodElement)
         {
             bool swt = true;
             //string strLeterHourWork = "";
             Microsoft.Office.Interop.Excel.Application xlsApp = null;//creo una aplicación Excel


             try
             {
                 xlsApp = new Microsoft.Office.Interop.Excel.Application();//creo una aplicación Excel
                 string temp = "";
                 int iColumnas = 0;


                 xlsApp.DisplayAlerts = false;
                 Microsoft.Office.Interop.Excel.Workbook xlsBook; //creo un libro
                 //Microsoft.Office.Interop.Excel.Worksheet[] xlsSheet; //creo una hoja

                 Microsoft.Office.Interop.Excel.Sheets sheets = null;
                 //xlsSheet = new Microsoft.Office.Interop.Excel.Worksheet[numTab];
                 xlsApp.Visible = true; //la aplicación no es visible

                 xlsBook = xlsApp.Workbooks.Add(true);//añado el libro a la aplicación
                 // oChart = (Microsoft.Office.Interop.Excel._Chart)xlsBook.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                 //Realizamos el grafico

                 Microsoft.Office.Interop.Excel.Worksheet xlsSheetGraf = (Microsoft.Office.Interop.Excel.Worksheet)xlsBook.ActiveSheet; //activo la hoja, para el libro
                 xlsSheetGraf.Name = "GRÁFICO";
                 for (int iCol = 0; iCol <= dgvDataGraf.Columns.Count; iCol++)
                 {
                     if (iCol == dgvDataGraf.Columns.Count)
                     {
                         xlsSheetGraf.Cells[3, iColumnas + 1] = "Límite Permisible";
                         break;
                     }

                     if (dgvDataGraf.Columns[iCol].Visible == true)
                     {
                         xlsSheetGraf.Cells[3, iColumnas + 1] = dgvDataGraf.Columns[iCol].HeaderText;

                         iColumnas++;
                     }
                 }


                 for (int iRow = 0; iRow < dgvDataGraf.Rows.Count; iRow++)
                 {
                     int intCol1 = 1;
                     for (int iCol = 0; iCol <= dgvDataGraf.Columns.Count; iCol++)
                     {

                         if (iCol == dgvDataGraf.Columns.Count)
                         {
                             xlsSheetGraf.Cells[iRow + 4, intCol1] = dblLimitPer;
                             break;
                         }

                         if (dgvDataGraf.Columns[iCol].Visible == true)
                         {
                             if (dgvDataGraf.Columns[iCol].Name.Equals("day"))
                             {
                                 xlsSheetGraf.Cells[iRow + 4, intCol1] = ((DateTime)dgvDataGraf[iCol, iRow].Value).ToString("yyyy-MM-dd");

                             }
                             else
                             {
                                 //if(dgvDataGraf.Columns[iCol].Name.Equals("Total") || dgvDataGraf.Columns[iCol].Name.Equals("Generacio%"))
                                 xlsSheetGraf.Cells[iRow + 4, intCol1] = dgvDataGraf[iCol, iRow].Value;

                             }

                           


                             intCol1++;

                         }
                     }


                     

                     //Microsoft.Office.Interop.Excel.Range rangoRowG = (Microsoft.Office.Interop.Excel.Range)xlsSheetGraf.get_Range("A" + (iRow + 4), columnTwoLetter(iColumnas - 3) + (iRow + 4));
                     ////rangoRow.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                     ////rangoRow.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                     ////Log.eLog("", "Color:" + dgvDataGraf.Rows[iRow].Cells["description"].Style.BackColor.Name);
                     //if (!dgvDataGraf.Rows[iRow].Cells["description"].Style.BackColor.Name.Equals("0"))
                     //    rangoRowG.Interior.Color = dgvDataGraf.Rows[iRow].Cells["description"].Style.BackColor;//System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.PowderBlue);
                     //if (dgvDataGraf.Rows[iRow].Cells["description"].Style.BackColor.Name.Equals("ff000000"))
                     //    rangoRowG.Cells.Font.Color = Color.White;

                     // xlsApp.Selection.Interior.Pattern = Microsoft.Office.Interop.Excel.;
                 }

                 
                 Microsoft.Office.Interop.Excel.Range rangoG = (Microsoft.Office.Interop.Excel.Range)xlsSheetGraf.get_Range("A3", columnTwoLetter(iColumnas) + (dgvDataGraf.Rows.Count + 3));

                 rangoG.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                 xlsSheetGraf.Columns.AutoFit();


                 //Cargamos datos para el grafico
                 graphAllGraphic(ref xlsApp, ref xlsBook, ref xlsSheetGraf, dgvDataGraf,dblLimitPer);

                

                 fileName = "";//Variable que almacenara el nombre del archivo.
                 while (fileName.Equals(""))
                 {
                     SaveFileDialog opdFile = new SaveFileDialog();
                     opdFile.Filter = "Archivo  Excel 2000-2003|*.xls|Archivo  Excel 2007|*.xlsx";
                     opdFile.Title = "Guardar archivo Excel.";

                     //opdFile.InitialDirectory=System.Computer.FileSystem.SpecialDirectories.MyDocuments;
                     if (opdFile.ShowDialog() == DialogResult.OK)
                     {
                         fileName = opdFile.FileName;
                         //xlsSheet.SaveAs(fileName);
                         xlsBook.SaveAs(fileName);
                     }

                     else
                     {
                         if (MessageBox.Show("¿La ruta no es válida desea continuar el guardado de archivo?", "Confirmar", MessageBoxButtons.YesNo) == DialogResult.No)
                         {
                             fileName = "xxxxxxx";
                             swt = false;
                         }
                         else
                         {
                             fileName = "";

                         }
                     }


                 }



                 xlsBook.Close();
                 // xlsApp.Quit();
             }
             catch (Exception e)
             {
                 throw e;
             }
             finally
             {
                 if (xlsApp != null)
                 {
                     xlsApp.Workbooks.Close();
                     xlsApp.Quit();
                 }

             }

             return swt;

         }




         public static bool initalAppExcel(ref Microsoft.Office.Interop.Excel.Application xlsApp,ref Microsoft.Office.Interop.Excel.Workbook xlsBook,string strUser){

              xlsApp = null;//creo una aplicación Excel


             try
             {
                 xlsApp = new Microsoft.Office.Interop.Excel.Application();//creo una aplicación Excel
                 
                 xlsApp.DisplayAlerts = false;
                 //Microsoft.Office.Interop.Excel.Worksheet[] xlsSheet; //creo una hoja

                 Microsoft.Office.Interop.Excel.Sheets sheets = null;
                 //xlsSheet = new Microsoft.Office.Interop.Excel.Worksheet[numTab];
                 xlsApp.Visible = true; //la aplicación no es visible

                 xlsBook = xlsApp.Workbooks.Add(true);//añado el libro a la aplicación
                 return true;


             }catch(Exception ex){
             
                 Log.eLog(strUser, "(ERROR:) Se generó errores al intentar crear una instancia de excel:"+ex.ToString());
                 return false;
             
             }
         
         
         }

         public static bool initalAppExcel(ref Microsoft.Office.Interop.Excel.Application xlsApp, ref Microsoft.Office.Interop.Excel.Workbook xlsBook, string strUser, bool  isVisible)
         {

             xlsApp = null;//creo una aplicación Excel


             try
             {
                 xlsApp = new Microsoft.Office.Interop.Excel.Application();//creo una aplicación Excel

                 xlsApp.DisplayAlerts = false;
                 //Microsoft.Office.Interop.Excel.Worksheet[] xlsSheet; //creo una hoja

                 Microsoft.Office.Interop.Excel.Sheets sheets = null;
                 //xlsSheet = new Microsoft.Office.Interop.Excel.Worksheet[numTab];
                 xlsApp.Visible = isVisible; //la aplicación no es visible

                 xlsBook = xlsApp.Workbooks.Add(true);//añado el libro a la aplicación
                 return true;


             }
             catch (Exception ex)
             {

                 Log.eLog(strUser, "(ERROR:) Se generó errores al intentar crear una instancia de excel:" + ex.ToString());
                 return false;

             }


         }

         public static bool exportExcel(string strTituloMain, string strTituloSecond, ref string fileName,
                   DataGridView dgvData, string strNameSheet, ref Microsoft.Office.Interop.Excel.Application xlsApp,
             ref Microsoft.Office.Interop.Excel.Workbook xlsBook)
         {
             bool swt = true;


             try
             {
                 string temp = "";
                 int iColumnas = 0;


                 Microsoft.Office.Interop.Excel.Sheets sheets = null;
                 Microsoft.Office.Interop.Excel.Worksheet xlsSheet = null;
                 sheets = xlsBook.Sheets as Microsoft.Office.Interop.Excel.Sheets; //Inicializar hoja

                 xlsSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
                 xlsApp.Visible = true; //la aplicación no es visible

                 //xlsBook = xlsApp.Workbooks.Add(true);//añado el libro a la aplicación
                 // oChart = (Microsoft.Office.Interop.Excel._Chart)xlsBook.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                 //Realizamos el grafico

                 //Microsoft.Office.Interop.Excel.Worksheet xlsSheetGraf = (Microsoft.Office.Interop.Excel.Worksheet)xlsBook.ActiveSheet; //activo la hoja, para el libro
                 xlsSheet.Name = strNameSheet;

                 xlsSheet.Cells[1, 1] = strTituloMain + " " + strNameSheet.ToUpper();


                 for (int iCol = 0; iCol < dgvData.Columns.Count; iCol++)
                 {


                     if (dgvData.Columns[iCol].Visible == true)
                     {
                         xlsSheet.Cells[3, iColumnas + 1] = dgvData.Columns[iCol].HeaderText;
                        //xlsApp.ActiveCell.Interior.ColorIndex = 36;

                         iColumnas++;
                     }
                 }

                 Microsoft.Office.Interop.Excel.Range rangoColor = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range("A3", columnTwoLetter(iColumnas - 1) + (3));
                 rangoColor.Interior.Color = Color.FromArgb(27, 109, 229);

                 for (int iRow = 0; iRow < dgvData.Rows.Count; iRow++)
                 {
                     int intCol1 = 1;
                     for (int iCol = 0; iCol < dgvData.Columns.Count; iCol++)
                     {



                         if (dgvData.Columns[iCol].Visible == true)
                         {
                             if (dgvData.Columns[iCol].Name.Equals("day"))
                             {
                                 xlsSheet.Cells[iRow + 4, intCol1] = ((DateTime)dgvData[iCol, iRow].Value).ToString("yyyy-MM-dd");

                             }
                             else
                             {
                                 //if(dgvDataGraf.Columns[iCol].Name.Equals("Total") || dgvDataGraf.Columns[iCol].Name.Equals("Generacio%"))
                                 xlsSheet.Cells[iRow + 4, intCol1] = dgvData[iCol, iRow].Value;

                             }




                             intCol1++;

                         }
                     }





                 }


                 Microsoft.Office.Interop.Excel.Range rangoG = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range("A3", columnTwoLetter(iColumnas-1) + (dgvData.Rows.Count + 3));

                 rangoG.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                 xlsSheet.Columns.AutoFit();

                 return true;


             }catch(Exception ex){
                 Log.eLog(null, "(ERROR:) Se obtuvo errores al intentar cargar datos en hoja excel:" + ex.ToString());
                 return false;
             }



                

         }



         public static bool exportExcel(string strTituloMain, string strTituloSecond, ref string fileName,
                     DataTable dtData, string strNameSheet, ref Microsoft.Office.Interop.Excel.Application xlsApp,
               ref Microsoft.Office.Interop.Excel.Workbook xlsBook, ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet,
               int intPosIni,int intNumColumns, int option)
         {
             bool swt = true;


             try
             {
                 string temp = "";
                 int iColumnas = 0;


                 //Microsoft.Office.Interop.Excel.Sheets sheets = null;
                 //Microsoft.Office.Interop.Excel.Worksheet xlsSheet = null;
                 //sheets = xlsBook.Sheets as Microsoft.Office.Interop.Excel.Sheets; //Inicializar hoja

                // xlsSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
                // xlsApp.Visible = true; //la aplicación no es visible

                 //xlsBook = xlsApp.Workbooks.Add(true);//añado el libro a la aplicación
                 // oChart = (Microsoft.Office.Interop.Excel._Chart)xlsBook.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                 //Realizamos el grafico

                 //Microsoft.Office.Interop.Excel.Worksheet xlsSheetGraf = (Microsoft.Office.Interop.Excel.Worksheet)xlsBook.ActiveSheet; //activo la hoja, para el libro
                 xlsSheet.Name = strNameSheet;


                 xlsSheet.Cells[5, intPosIni + intNumColumns] = strTituloMain;


                 //for (int iCol = 0; iCol < dtData.Columns.Count; iCol++)
                 //{


                 //    if (option == 1)
                 //    {
                 //        if (dtData.Columns[iCol].ColumnName.Equals("Ag"))
                            
                 //            xlsSheet.Cells[7, iColumnas + 1] = "Oz "+dtData.Columns[iCol].ColumnName;
                 //        else if (!dtData.Columns[iCol].ColumnName.Equals("TMS") && !dtData.Columns[iCol].ColumnName.Equals("Stock  Mes Anterior"))
                 //            xlsSheet.Cells[7, iColumnas + 1] = "%" + dtData.Columns[iCol].ColumnName;
                 //        else
                 //            xlsSheet.Cells[7, iColumnas + 1] = dtData.Columns[iCol].ColumnName;

                 //        //xlsApp.ActiveCell.Interior.ColorIndex = 36;
                 //    }
                 //        iColumnas++;
                 //    //}
                 //}
                 iColumnas = dtData.Columns.Count-1;

                 //Microsoft.Office.Interop.Excel.Range rangoColor = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range("A3", columnTwoLetter(iColumnas - 1) + (3));
                // rangoColor.Interior.Color = Color.FromArgb(27, 109, 229);

                 for (int iRow = 0; iRow < dtData.Rows.Count; iRow++)
                 {
                     int intCol1 = 2;
                     for (int iCol = 0; iCol < dtData.Columns.Count; iCol++)
                     {

                                                       //if(dgvDataGraf.Columns[iCol].Name.Equals("Total") || dgvDataGraf.Columns[iCol].Name.Equals("Generacio%"))
                         //if (dtData.Columns[iCol].ColumnName.Equals("dt_registry"))
                         //{
                         //    xlsSheet.Cells[iRow + 4, intCol1] = ((DateTime)dtData.Rows[iRow][iCol]).ToString("yyyy-MM-dd");

                         //}
                         //else
                         //{
                               if(dtData.Columns[iCol].ColumnName.Equals("Ag")){
                                   xlsSheet.Cells[iRow + 9, intCol1] =Convert.ToDouble((dtData.Rows[iRow][iCol] != null && !dtData.Rows[iRow][iCol].ToString().Trim().Equals("")) ? dtData.Rows[iRow][iCol] : "0") * 31.1034768;
                                   xlsSheet.Cells[iRow + 9, intCol1].Value=(Convert.ToDouble(xlsSheet.Cells[iRow + 9, intCol1].Value)==0?"":xlsSheet.Cells[iRow + 9, intCol1].Value);
                                   }
                               else
                                   xlsSheet.Cells[iRow + 9, intCol1] = dtData.Rows[iRow][iCol];

                        

                         //}
                             if (dtData.Columns[iCol].ColumnName.Equals("Stock  Mes Anterior"))
                             {
                                 Microsoft.Office.Interop.Excel.Range rango2 = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(columnTwoLetter(intCol1-1) + (iRow + 9));
                                 rango2.Select();
                                 
                                 xlsApp.Selection.NumberFormat = "dd-mmm";

                             }


                             intCol1++;

                         }
                     }





                 


                 //Microsoft.Office.Interop.Excel.Range rangoG = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range("A3", columnTwoLetter(iColumnas - 1) + (dtData.Rows.Count + 3));

                 //rangoG.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                 //xlsSheet.Columns.AutoFit();

                 return true;


             }
             catch (Exception ex)
             {
                 Log.eLog(null, "(ERROR:) Se obtuvo errores al intentar cargar datos en hoja excel:" + ex.ToString());
                 return false;
             }





         }

        

         public static bool exportExcelOnlyOneSheet(string strTituloMain, string strTituloSecond, ref string fileName,
                    DataGridView dgvData, string strNameSheet, ref Microsoft.Office.Interop.Excel.Application xlsApp,
              ref Microsoft.Office.Interop.Excel.Workbook xlsBook, ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet)
         {
             
             try
             {
                 int iColumnas = 0;
                        
                 xlsSheet.Name = strNameSheet;

                 xlsSheet.Cells[1, 1] = strTituloMain + " " + strNameSheet.ToUpper();


                 for (int iCol = 0; iCol < dgvData.Columns.Count; iCol++)
                 {


                     if (dgvData.Columns[iCol].Visible == true)
                     {
                         xlsSheet.Cells[3, iColumnas + 1] = dgvData.Columns[iCol].HeaderText;
                         //xlsApp.ActiveCell.Interior.ColorIndex = 36;

                         iColumnas++;
                     }
                 }

                 Microsoft.Office.Interop.Excel.Range rangoColor = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range("A3", columnTwoLetter(iColumnas - 1) + (3));
                 rangoColor.Interior.Color = Color.FromArgb(27, 109, 229);

                 for (int iRow = 0; iRow < dgvData.Rows.Count; iRow++)
                 {
                     int intCol1 = 1;
                     for (int iCol = 0; iCol < dgvData.Columns.Count; iCol++)
                     {



                         if (dgvData.Columns[iCol].Visible == true)
                         {
                             if (dgvData.Columns[iCol].Name.Equals("day"))
                             {
                                 xlsSheet.Cells[iRow + 4, intCol1] = ((DateTime)dgvData[iCol, iRow].Value).ToString("yyyy-MM-dd");

                             }
                             else
                             {
                                 //if(dgvDataGraf.Columns[iCol].Name.Equals("Total") || dgvDataGraf.Columns[iCol].Name.Equals("Generacio%"))
                                 xlsSheet.Cells[iRow + 4, intCol1] = dgvData[iCol, iRow].Value;

                             }




                             intCol1++;

                         }
                     }





                 }


                 Microsoft.Office.Interop.Excel.Range rangoG = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range("A3", columnTwoLetter(iColumnas - 1) + (dgvData.Rows.Count + 3));

                 rangoG.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                 xlsSheet.Columns.AutoFit();

                 return true;


             }
             catch (Exception ex)
             {
                 Log.eLog(null, "(ERROR:) Se obtuvo errores al intentar cargar datos en hoja excel:" + ex.ToString());
                 return false;
             }





         }

         public static void initializateSheet(ref Microsoft.Office.Interop.Excel.Workbook xlsBook,ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet)
         {

            Microsoft.Office.Interop.Excel.Sheets sheets = null;
            sheets = xlsBook.Sheets as Microsoft.Office.Interop.Excel.Sheets; //Inicializar hoja

            xlsSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
        
        
        }

         public static void saveFileExcel(string fileName,ref  Microsoft.Office.Interop.Excel.Application xlsApp,
            ref  Microsoft.Office.Interop.Excel.Workbook xlsBook, bool swt)
         {
           
             try{
                 fileName = "";//Variable que almacenara el nombre del archivo.
                 while (fileName.Equals(""))
                 {
                     SaveFileDialog opdFile = new SaveFileDialog();
                     opdFile.Filter = "Archivo  Excel 2000-2003|*.xls|Archivo  Excel 2007|*.xlsx";
                     opdFile.Title = "Guardar archivo Excel.";

                     //opdFile.InitialDirectory=System.Computer.FileSystem.SpecialDirectories.MyDocuments;
                     if (opdFile.ShowDialog() == DialogResult.OK)
                     {
                         fileName = opdFile.FileName;
                         //xlsSheet.SaveAs(fileName);
                         xlsBook.SaveAs(fileName);
                     }

                     else
                     {
                         if (MessageBox.Show("¿La ruta no es válida desea continuar el guardado de archivo?", "Confirmar", MessageBoxButtons.YesNo) == DialogResult.No)
                         {
                             fileName = "xxxxxxx";
                             swt = false;
                         }
                         else
                         {
                             fileName = "";

                         }
                     }


                 }



                 xlsBook.Close();
                 // xlsApp.Quit();
             }
             catch (Exception e)
             {
                 throw e;
             }
             finally
             {
                 if (xlsApp != null)
                 {
                     xlsApp.Workbooks.Close();
                     xlsApp.Quit();
                 }

             }

         
         }


         public static void saveFileExcelSource(ref string fileName, ref  Microsoft.Office.Interop.Excel.Application xlsApp,
             ref  Microsoft.Office.Interop.Excel.Workbook xlsBook,string strDefaultName,ref  bool swt)
         {

             try
             {
                 fileName = "";//Variable que almacenara el nombre del archivo.
                 while (fileName.Equals(""))
                 {
                     SaveFileDialog opdFile = new SaveFileDialog();

                     
                     opdFile.Filter = "Archivo  Excel 2007-2010|*.xlsx|Archivo  Excel 2003|*.xls";
                     opdFile.Title = "Guardar archivo Excel.";
                     opdFile.FileName = strDefaultName;

                     //opdFile.InitialDirectory=System.Computer.FileSystem.SpecialDirectories.MyDocuments;
                     if (opdFile.ShowDialog() == DialogResult.OK)
                     {
                         fileName = opdFile.FileName;
                         //xlsSheet.SaveAs(fileName);
                         xlsBook.SaveAs(fileName);
                     }

                     else
                     {
                         if (MessageBox.Show("¿La ruta no es válida desea continuar el guardado de archivo?", "Confirmar", MessageBoxButtons.YesNo) == DialogResult.No)
                         {
                             fileName = "xxxxxxx";
                             swt = false;
                         }
                         else
                         {
                             fileName = "";

                         }
                     }


                 }



                 xlsBook.Close();
                 // xlsApp.Quit();
             }
             catch (Exception e)
             {
                 throw e;
             }
             finally
             {
                 if (xlsApp != null)
                 {
                     xlsApp.Workbooks.Close();
                     xlsApp.Quit();
                 }

             }


         }



         private static  void graphAllTypeResidue( ref Excel.Application xlsApp, ref Excel.Workbook xlWorkBook, 
             ref Excel.Worksheet xlWorkSheet,DataGridView dgv)
         {                       
            
             //object misValue = System.Reflection.Missing.Value;

             //xlApp = new Excel.Application();
             //xlWorkBook = xlApp.Workbooks.Add(misValue);
             //xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
             //xlApp.Visible = true;


             Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
             Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(400, 20, 500, 250);
             Excel.Chart chartPage = myChart.Chart;
             Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection(Type.Missing);
             chartPage.ChartType = Excel.XlChartType.xlCylinderColClustered;


            
             //Microsoft.Office.Interop.Excel.Series serie = seriesCollection.NewSeries();
             //Microsoft.Office.Interop.Excel.Series serie2 = seriesCollection.NewSeries();

            
             //serie2.XValues = xlWorkSheet.get_Range("A1", "A" + 5);
             //serie2.Values = xlWorkSheet.get_Range("D1", "D" + 5);

             //for (int i = 0; i < dgv.RowCount-1; i++)
             //{
             //    Microsoft.Office.Interop.Excel.Series serie = seriesCollection.NewSeries();
             //    serie.XValues = xlWorkSheet.get_Range("A"+(4+i), "A" +(4+i));
             //    serie.Values = xlWorkSheet.get_Range("C"+(4+i), "C" +(4+i));
             //    serie.Name = dgv.Rows[i].Cells["description"].Value.ToString();
             //    serie.ApplyDataLabels();
                 
             //    //serie.Points(0).Select();
             //    //xlsApp.Selection.Format.Fill();
             

             //}
             Excel.Series serie = seriesCollection.NewSeries();
             serie.XValues = xlWorkSheet.get_Range("A4", "A" + (4 + dgv.Rows.Count - 2));
             serie.Values = xlWorkSheet.get_Range("C4", "C" + (4 + dgv.Rows.Count - 2));

             for(int i=0; i<dgv.Rows.Count-1 ; i++){
                 int intR = Convert.ToInt32(dgv.Rows[i].Cells["R"].Value);
                 int intG = Convert.ToInt32(dgv.Rows[i].Cells["G"].Value);
                 int intB = Convert.ToInt32(dgv.Rows[i].Cells["B"].Value);
                 Excel.Point point=  serie.Points(i+1);
                 point.Select();
                 xlsApp.Selection.Format.Fill().ForeColor.RGB = Color.FromArgb(intR,intG,intB); //Excel.XlRgbColor.rgbAquamarine;
                 // xlsApp.Selection.Format.Fill().ForeColor.ObjectThemeColor = Excel.XlThemeColor.xlThemeColorAccent2;
             }

         }

         private static void graphAllGraphic(ref Excel.Application xlsApp, ref Excel.Workbook xlWorkBook, ref Excel.Worksheet xlWorkSheet, DataGridView dgv,
             double dblLimintPer)
         {
                        
             Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
             Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(400, 20, 500, 250);
             Excel.Chart chartPage = myChart.Chart;
             Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection(Type.Missing);
             
             chartPage.ChartType = Excel.XlChartType.xlLine;
             
             
             for (int j = 1; j <= dgv.Columns.Count; j++)
             {


                 Excel.Series serie = seriesCollection.NewSeries();
                 if (j == dgv.Columns.Count)
                 {
                     serie.Name = "Límite Permisible";
                     
                     serie.XValues = xlWorkSheet.get_Range("A4", "A" + (4 + dgv.Rows.Count -1));
                     serie.Values = xlWorkSheet.get_Range(Util.columnTwoLetter(j) + "4", Util.columnTwoLetter(j) + (4 + dgv.Rows.Count - 1));
                     serie.ApplyDataLabels();
                     break;
                 }

                 serie.Name = dgv.Columns[j].Name;
                 serie.XValues = xlWorkSheet.get_Range("A4", "A" + (4 + dgv.Rows.Count - 1));
                 serie.Values = xlWorkSheet.get_Range(Util.columnTwoLetter(j) + "4", Util.columnTwoLetter(j) + (4 + dgv.Rows.Count - 1));
                 serie.ApplyDataLabels();
                 //for (int i = 0; i < dgv.Rows.Count - 1; i++)
                 //{
                 //    int intR = Convert.ToInt32(dgv.Rows[i].Cells["R"].Value);
                 //    int intG = Convert.ToInt32(dgv.Rows[i].Cells["G"].Value);
                 //    int intB = Convert.ToInt32(dgv.Rows[i].Cells["B"].Value);
                 //    Excel.Point point = serie.Points(i + 1);
                 //    point.Select();
                 //    xlsApp.Selection.Format.Fill().ForeColor.RGB = Color.FromArgb(intR, intG, intB); //Excel.XlRgbColor.rgbAquamarine;
                 //    // xlsApp.Selection.Format.Fill().ForeColor.ObjectThemeColor = Excel.XlThemeColor.xlThemeColorAccent2;
                 //}



             }
             

         }

       
       
       
        private static void getCombineCell(ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet, string strLetter, int row)
         {
             Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLetter + row, strLetter + (row + 1));
             range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
             range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
             range.WrapText = false;
             range.Orientation = 0;
             range.AddIndent = false;
             range.IndentLevel = 0;
             range.ShrinkToFit = false;
             //range.ReadingOrder = xlContext; 
             range.MergeCells = false;
             range.Merge();
             
         }
       

        public static void setFormatNumber(ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet, string strLetter1, string strLetter2)
        {
            Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLetter1, strLetter2);
            range.NumberFormat = "0.00";

        }

        public static void setFormatNumber(ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet, string strLetter1, string strLetter2, string strFormat)
        {
            Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLetter1, strLetter2);
            range.NumberFormat = strFormat;

        }

        public static void getCombineCell(ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet, string strLetter1, string strLetter2)
        {
            Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLetter1, strLetter2);
            range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
            range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
            range.WrapText = true;
            range.Orientation = 0;
            range.AddIndent = false;
            range.IndentLevel = 0;
            range.ShrinkToFit = false;
            //range.ReadingOrder = xlContext; 
            range.MergeCells = true;
            range.Merge();
            //range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
            //range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

        }

        public static void setCellCenter(ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet, string strLetter1, string strLetter2)
        {
            Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLetter1, strLetter2);
            range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
            range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
            range.WrapText = false;
            range.Orientation = 0;
            range.AddIndent = false;
            range.IndentLevel = 0;
            range.ShrinkToFit = false;
            //range.ReadingOrder=Microsoft.Office.Interop.Excel.
            range.MergeCells = false;
            
           

        }

        public static void setCellAjustText(ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet, string strLetter1, string strLetter2)
        {
            Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLetter1, strLetter2);
           // range.Select();
            range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignGeneral;
            range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignBottom;
            range.WrapText = true;
            range.Orientation = 0;
            range.AddIndent = false;
            range.IndentLevel = 0;
            range.ShrinkToFit = false;
            //range.ReadingOrder=Microsoft.Office.Interop.Excel.
            range.MergeCells = false;



        }
        public static void getCombineCellLeft(ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet, string strLetter1, string strLetter2)
        {
            Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLetter1, strLetter2);
            range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
            range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;
            range.WrapText = true;
            range.Orientation = 0;
            range.AddIndent = false;
            range.IndentLevel = 0;
            range.ShrinkToFit = false;
            //range.ReadingOrder = xlContext; 
            range.MergeCells = true;
            range.Merge();
            //range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
            //range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

        }

        public static void getCombineCell2(ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet, string strLetter1, string strLetter2)
        {
            Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLetter1, strLetter2);
            range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
            range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
            range.WrapText = false;
            range.Orientation = 0;
            range.AddIndent = false;
            range.IndentLevel = 0;
            range.ShrinkToFit = false;
            //range.ReadingOrder = xlContext; 
            range.MergeCells = false;
            range.Merge();
            range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

        }

        public static void getBorders(ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet,string strLetter1, string strLetter2)
        {
            Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLetter1, strLetter2);
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;

            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].ColorIndex = 0;
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].TintAndShade = 0;
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;

            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].ColorIndex = 0;
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].TintAndShade = 0;
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;

            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].ColorIndex = 0;
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].TintAndShade = 0;
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;

            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].ColorIndex = 0;
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].TintAndShade = 0;
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;

            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
        }


         public static void getBordersSimple(ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet,string strLetter1, string strLetter2)
        {
            Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLetter1, strLetter2);
           

            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
           

          

        }

         public static void getBordersSimple(ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet, string strLetter1, string strLetter2, Color color)
         {
             Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLetter1, strLetter2);


             range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
             range.Borders.Color = color;



         }

         public static void setColorInterior(ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet, string strLetter1, string strLetter2, Color color)
         {
             Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLetter1, strLetter2);


             range.Interior.Color = color;



         }

         public static void setColorLetter(ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet, string strLetter1, string strLetter2, Color color)
         {
             Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLetter1, strLetter2);

             
             range.Font.Color= color;



         }


         public static void setComment(ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet, string strLetter1, string strLetter2, string txtComment)
         {
             Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLetter1, strLetter2);


             range.AddComment();
             range.Comment.Visible = false;
             range.Comment.Text(txtComment);


         }

         public static void setColorLetterAndInterior(ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet, string strLetter1, string strLetter2, Color colorLetter, Color colorInt)
         {
             Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLetter1, strLetter2);

            
             range.Font.Color = colorLetter;
             range.Interior.Color = colorInt;



         }






         public static void setWidthColumn(ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet, string strLetter1, string strLetter2,int intWidth)
         {
             Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLetter1, strLetter2);
             //range.Select();
             range.ColumnWidth = intWidth;
             
         }
         public static void setHeightRow(ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet, string strLetter1, string strLetter2, int intHeight)
         {
             Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLetter1, strLetter2);
             //range.Select();
             range.RowHeight= intHeight;




         }

         public static void setBold(ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet, string strLetter1, string strLetter2)
         {
             Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLetter1, strLetter2);

             range.Font.Bold = true;




         }

         public static void setSizeAndFuente(ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet, string strLetter1, string strLetter2, int intSize, string fuente)
         {
             Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLetter1, strLetter2);

             range.Font.Size = intSize;
             range.Font.Name = fuente;



         }

         public static void setHideRow(ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet, string strLetter1)
         {
             Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLetter1, strLetter1);

             range.EntireRow.Hidden = true;

         }

         public static void setSizeFuenteAndBold(ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet, string strLetter1, string strLetter2, int intSize, string fuente, Color color,
             bool boolBold)
         {
             Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLetter1, strLetter2);

             range.Font.Size = intSize;
             range.Font.Name = fuente;
             range.Font.Bold = boolBold;
             range.Font.Color = color;


         }

         public static void setUnderline(ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet, string strLetter1, string strLetter2)
         {
             Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLetter1, strLetter2);

             range.Font.Underline=Microsoft.Office.Interop.Excel.XlUnderlineStyle.xlUnderlineStyleSingle;
            


         }


         public static void setFuente(ref Microsoft.Office.Interop.Excel.Worksheet xlsSheet, string strLetter1, string strLetter2,string fuente)
         {
             Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlsSheet.get_Range(strLetter1, strLetter2);
             range.Font.Name = fuente;



         }



         public static string columnTwoLetter(int num)
         {
             // Dado un número de columna entre 1 y 256
             // la función devuelve una cadena de una o dos letras que indican
             // el nombre de la columna en Excel entre la A y la IV.
             string[] letras = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
             string columna_letra;

             int aux;

             if (num < 26)
                 columna_letra = letras[num];
             else
             {
                 aux = (int)decimal.Truncate(num / 26);
                 columna_letra = letras[aux-1];
                 num = num - (26 * aux);
                 columna_letra +=columnTwoLetter(num);
             }
             //Log.eLog(null, "Letra Excel " + columna_letra);
             return (columna_letra);
         }


         public static bool validateHour(string srtHourStart, string srtHourFinish, string strUser)
         {
             try
             {


                 DateTime dtStart = DateTime.ParseExact(DateTime.Now.ToString("yyyy-MM-dd " + srtHourStart + ":00"), "yyyy-MM-dd HH:mm:ss", null);
                 DateTime dtFinish = DateTime.ParseExact(DateTime.Now.ToString("yyyy-MM-dd " + srtHourFinish + ":00"), "yyyy-MM-dd HH:mm:ss", null);

                 if ((dtFinish - dtStart).Hours < 0)
                 {
                     return false;
                 }
                 else if ((dtFinish - dtStart).Hours == 0)
                 {

                     if ((dtFinish - dtStart).Minutes < 0)
                     {


                         return false;

                     }
                     else if ((dtFinish - dtStart).Minutes == 0)
                     {

                         return false;
                     }
                 }






             }
             catch (Exception ex)
             {

                 Log.eLog(strUser, "Errorar al intentar calcular el parseo de la fecha " + ex.ToString());
                  return false;
             }

             // if(

             return true;
         }



       public static string  getNameMounth(int  intNumberMonth){


           switch(intNumberMonth){

               case 1:return "ENERO";
               case 2: return "FEBRERO";
               case 3: return "MARZO";
               case 4: return "ABRIL";
               case 5: return "MAYO";
               case 6: return "JUNIO";
               case 7: return "JULIO";
               case 8: return "AGOSTO";
               case 9: return "SEPTIEMBRE";
               case 10: return "OCTUBRE";
               case 11: return "NOVIEMBRE";
               case 12: return "DICIEMBRE";
               default: return "Desconocido";


           }
       }


       public static string getNameDay(int intNumberDay)
       {


           switch (intNumberDay)
           {

               case 1: return "UNO";
               case 2: return "DOS";
               case 3: return "TRES";
               case 4: return "CUATRO";
               case 5: return "CINCO";
               case 6: return "SEIS";
               case 7: return "SIETE";
               case 8: return "OCHO";
               case 9: return "NUEVE";
               case 10: return "DIEZ";
               case 11: return "ONCE";
               case 12: return "DOCE";
               case 13: return "TRECE";
               case 14: return "CATORCE";
               case 15: return "QUINCE";
               case 16: return "DIECISEIS";
               case 17: return "DIECISEIS";
               case 18: return "DIECIOCHO";
               case 19: return "DIECINUEVE";
               case 20: return "VEINTE";
               case 21: return "VEINTIUNO";
               case 22: return "VEINTIDOS";
               case 23: return "VEINTITRES";
               case 24: return "VEINTICUATRO";
               case 25: return "VEINTICINCO";
               case 26: return "VEINTISEIS";
               case 27: return "VEINTISIETE";
               case 28: return "VEINTIOCHO";
               case 29: return "VEINTINUEVE";
               case 30: return "TREINTA";
               case 31: return "TREINTIUNO";
               default: return "DESCONOCIDO";


           }
       }

       public static bool createDirectory(){

           //Guardamos la ruta dentro de la carpeta donde ejecutamos el programa
           string path = Application.StartupPath + "/logs";
           //Creamos un objeto de tipo directoryinfo y le pasamos la ruta donde se va a crear
           DirectoryInfo Dir = new DirectoryInfo(path);

           try
           {
               //Verificamos si existe la carpeta
               if (!Dir.Exists)
               {
                   //Creamos la carpeta
                   Dir.Create();
                   ////Por si desean hacer la carpeta con atributo oculto
                   //Dir.Attributes = FileAttributes.Hidden;
                   ////Creamos un archivo de texto dentro de la carpeta
                   //File.Create(path + "/ejemplo.txt");
                   ////Por si desean hacer el archivo con atributo oculto
                   //File.SetAttributes(path + "/ejemplo.txt", FileAttributes.Hidden);
                   ////Abrimos la carpeta
                   //System.Diagnostics.Process.Start(@path);
               }
               return true;
           }catch{

               return false;
           }
           
       }

   
       public static bool validateTracks(ref string msg, DataTable dtDetailPerf, string fieldOne ,string FieldTwo, string user)
       {

           double dblHourTotal = 0;


           for (int i = 0; i < dtDetailPerf.Rows.Count; i++)
           {
               TimeSpan tsp = ((DateTime)dtDetailPerf.Rows[i][FieldTwo]) - ((DateTime)dtDetailPerf.Rows[i][fieldOne]);
               dblHourTotal = dblHourTotal + (tsp.Hours + Convert.ToDouble(tsp.Minutes) / 60);
           }

           Log.eLog(user, "Hora acumulada:" + dblHourTotal);

           if (Math.Round(dblHourTotal,2) < 11.50)
           {

               msg = "La hora acumulada es " + Math.Round(dblHourTotal, 2).ToString("0.00") + ", debería ser exactamente 10.50 horas";
               return false;


           }
           else if (Math.Round(dblHourTotal ,2)> 11.50)
           {

               msg = "La hora acumulada es " + Math.Round(dblHourTotal, 2).ToString("0.00") + ", debería ser exactamente 10.50 horas";
               return false;
           }

           return true;
       }

       public static void setColorCell(ref DataGridView dgvDetail, int intRow)
       {


           if (dgvDetail.Rows[intRow].Cells["rich_classif"].Value.ToString().Trim().Length > 0
                       && dgvDetail.Rows[intRow].Cells["rich_classif"].Value.ToString().Trim().Substring(0, 1).Equals("E"))
               dgvDetail.Rows[intRow].Cells["rich_classif"].Style.BackColor = Color.Red;
           else if (dgvDetail.Rows[intRow].Cells["rich_classif"].Value.ToString().Trim().Length > 0
               && dgvDetail.Rows[intRow].Cells["rich_classif"].Value.ToString().Trim().Substring(0, 1).Equals("A"))
               dgvDetail.Rows[intRow].Cells["rich_classif"].Style.BackColor = Color.FromArgb(92, 56, 224);
           else if (dgvDetail.Rows[intRow].Cells["rich_classif"].Value.ToString().Trim().Length > 0
                && dgvDetail.Rows[intRow].Cells["rich_classif"].Value.ToString().Trim().Substring(0, 1).Equals("M"))
               dgvDetail.Rows[intRow].Cells["rich_classif"].Style.BackColor = Color.Green;
           else if (dgvDetail.Rows[intRow].Cells["rich_classif"].Value.ToString().Trim().Length > 0
               && dgvDetail.Rows[intRow].Cells["rich_classif"].Value.ToString().Trim().Substring(0, 1).Equals("B"))
               dgvDetail.Rows[intRow].Cells["rich_classif"].Style.BackColor = Color.Yellow;

       }

       public static void setColorCell(ref DataGridView dgvDetail, int intRow, string nameColumn)
       {


           if (dgvDetail.Rows[intRow].Cells[nameColumn].Value.ToString().Trim().Length > 0
                       && dgvDetail.Rows[intRow].Cells[nameColumn].Value.ToString().Trim().Substring(0, 1).Equals("E"))
               dgvDetail.Rows[intRow].Cells[nameColumn].Style.BackColor = Color.Red;
           else if (dgvDetail.Rows[intRow].Cells[nameColumn].Value.ToString().Trim().Length > 0
               && dgvDetail.Rows[intRow].Cells[nameColumn].Value.ToString().Trim().Substring(0, 1).Equals("A"))
               dgvDetail.Rows[intRow].Cells[nameColumn].Style.BackColor = Color.FromArgb(92, 56, 224);
           else if (dgvDetail.Rows[intRow].Cells[nameColumn].Value.ToString().Trim().Length > 0
                && dgvDetail.Rows[intRow].Cells[nameColumn].Value.ToString().Trim().Substring(0, 1).Equals("M"))
               dgvDetail.Rows[intRow].Cells[nameColumn].Style.BackColor = Color.Green;
           else if (dgvDetail.Rows[intRow].Cells[nameColumn].Value.ToString().Trim().Length > 0
               && dgvDetail.Rows[intRow].Cells[nameColumn].Value.ToString().Trim().Substring(0, 1).Equals("B"))
               dgvDetail.Rows[intRow].Cells[nameColumn].Style.BackColor = Color.Yellow;

       }
       
       public static Bitmap Resize(string Img, int porCiento, string strUser) {

           try
           {
               //Creamos un bitmap de la imagen importando el fichero de disco.
               Bitmap image = (Bitmap)Bitmap.FromFile(Img);

               //Calculamos el % que vamos a redimensionar la imagen
               float nPercent = ((float)porCiento / 100);

               //Obtenemos el actual ancho y alto.
               int destinoWidth = (int)(image.Width * nPercent);
               int destinoHeight = (int)(image.Height * nPercent);

               //Y aquí es donde redimensionamos la imagen, creamos un segundo bitmap de la imagen con las nuevas medidas
               Bitmap Imagen2 = new Bitmap(destinoWidth, destinoHeight);

               //Y creamos la imagen con la clase Graphics
               using (Graphics g = Graphics.FromImage((Image)Imagen2))
               {
                   g.DrawImage(image, 0, 0, destinoWidth, destinoHeight);
               }

               //Por último eliminamos referencias y devolvemos la imagen al método que ha llamado a esta clase.
               image.Dispose();
               return (Imagen2);
           }catch(Exception ex){
               Log.eLog(strUser, "(ERROR): Se obtuvieron errores al intentar redimensionar imagen:" + Img + ":" + ex.ToString());
               return null;
           }

     }

       public static Image Resize(Image image, int intWidth, int intHeigth, string strUser)
       {

           try
           {
               //Creamos un bitmap de la imagen importando el fichero de disco.
              // Bitmap image = (Bitmap)Bitmap.FromFile(Img);
                             
               
               //Obtenemos el actual ancho y alto.
               int destinoWidth = intWidth;
               int destinoHeight = intHeigth;

               //Y aquí es donde redimensionamos la imagen, creamos un segundo bitmap de la imagen con las nuevas medidas
               Bitmap Imagen2 = new Bitmap(destinoWidth, destinoHeight);

               //Y creamos la imagen con la clase Graphics
               using (Graphics g = Graphics.FromImage((Image)Imagen2))
               {
                   g.DrawImage(image, 0, 0, destinoWidth, destinoHeight);
               }

               //Por último eliminamos referencias y devolvemos la imagen al método que ha llamado a esta clase.
               image.Dispose();
               return ((Image)Imagen2);
           }
           catch (Exception ex)
           {
               Log.eLog(strUser, "(ERROR): Se obtuvieron errores al intentar redimensionar imagen:"  + ex.ToString());
               return null;
           }

       }


       public static Image Resize(string img, int intWidth, int intHeigth, string strUser)
       {

           try
           {
               //Creamos un bitmap de la imagen importando el fichero de disco.
                Bitmap image = (Bitmap)Bitmap.FromFile(img);


               //Obtenemos el actual ancho y alto.
               int destinoWidth = intWidth;
               int destinoHeight = intHeigth;

               //Y aquí es donde redimensionamos la imagen, creamos un segundo bitmap de la imagen con las nuevas medidas
               Bitmap Imagen2 = new Bitmap(destinoWidth, destinoHeight);

               //Y creamos la imagen con la clase Graphics
               using (Graphics g = Graphics.FromImage((Image)Imagen2))
               {
                   g.DrawImage(image, 0, 0, destinoWidth, destinoHeight);
               }

               //Por último eliminamos referencias y devolvemos la imagen al método que ha llamado a esta clase.
               image.Dispose();
               return ((Image)Imagen2);
           }
           catch (Exception ex)
           {
               Log.eLog(strUser, "(ERROR): Se obtuvieron errores al intentar redimensionar imagen:"+img +":" +ex.ToString());
               return null;
           }

       }


       public static  bool IsValidEmailAddress(string email)
       {
           try
           {
               MailAddress ma = new MailAddress(email);

               return true;
           }
           catch
           {
               return false;
           }
       }



       public static DataTable getRecuperation(int intPosCol,DataGridView dgvData, string strDistinctElement,double dblTmh, double dblH2O,double dblTms )
       {
           //Calculamos las recuperacioens de cada elemento

           //Cargamos la estructura de la tabla recuperacion.

           DataTable dtRecuperation=new DataTable();

           loadEstructureRecuperation(ref dtRecuperation);
           dtRecuperation.Rows[0]["tms"] = dblTms;
           dtRecuperation.Rows[0]["peso"] = 100;
           double dblWeight = 0;

           for (int j = intPosCol; j < dgvData.Columns.Count; j++)
           {

               DataTable dtDetNum = maxtrixDeterm(4);
               DataTable dtDetNum2 = maxtrixDeterm(3);
               DataTable dtDetDen = maxtrixDeterm(4);
               DataTable dtDetDen2 = maxtrixDeterm(3);

               loadMatrix(dgvData, strDistinctElement, intPosCol, ref dtDetNum, j);

               loadMatrixDen(dgvData, strDistinctElement, intPosCol, ref dtDetDen, j);
               
               //realizamos calculos en la matrices auxiliares.
               calculateMatrixDef(dtDetNum, ref dtDetNum2);
               calculateMatrixDef(dtDetDen, ref dtDetDen2);

               double dblNumDet = calculateDet(dtDetNum2);
               double dblDenDet = calculateDet(dtDetDen2);

               double dblDet = 0;
               if (dblNumDet == 0 || dblDenDet == 0)
                   dblDet = 0;
               else
                   dblDet =Math.Abs( (dblNumDet / dblDenDet )* 100);

               //Cargamos datos del calculados de las determinantes
               dblWeight += dblDet;
               loadDeterminateForElement(ref dtRecuperation, dgvData.Columns[j].Name.Trim(), dblDet);

               
           }

           //Calculamos relave y ley cabeza calculada

           loadRelaveAndCabeza(ref dtRecuperation, dblWeight, dblTms);
           //Cargamos leyes
           loadDataLeyLab(dgvData, ref dtRecuperation, intPosCol);

           //Calculamos Concentrado Metalico
           loadDataContentMetalico(dgvData, ref dtRecuperation, intPosCol);
           //Calculamos 
           loadDataDistribution(ref dtRecuperation, intPosCol);

           //Calculamos total de ensayo
           loadDataTotalEnsayo(ref dtRecuperation, intPosCol);

           //Total Recuperacion
           DataTable dtGranTotal=loadDataTotalRecuperataion(ref dtRecuperation, intPosCol);

           return dtGranTotal;
       }



       //Metodo para extraer el reporte metalurgico por guardia
       public static DataTable getRecuperation(int intPosCol, DataGridView dgvData, string strDistinctElement, double dblTmh, double dblH2O, double dblTms,int option)
       {
           //Calculamos las recuperacioens de cada elemento

           //Cargamos la estructura de la tabla recuperacion.

           DataTable dtRecuperation = new DataTable();

           loadEstructureRecuperation(ref dtRecuperation);
           dtRecuperation.Rows[0]["tms"] = dblTms;
           dtRecuperation.Rows[0]["peso"] = 100;
           double dblWeight = 0;

           for (int j = intPosCol; j < dgvData.Columns.Count; j++)
           {

               DataTable dtDetNum = maxtrixDeterm(4);
               DataTable dtDetNum2 = maxtrixDeterm(3);
               DataTable dtDetDen = maxtrixDeterm(4);
               DataTable dtDetDen2 = maxtrixDeterm(3);

               loadMatrix(dgvData, strDistinctElement, intPosCol, ref dtDetNum, j);

               loadMatrixDen(dgvData, strDistinctElement, intPosCol, ref dtDetDen, j);

               //realizamos calculos en la matrices auxiliares.
               calculateMatrixDef(dtDetNum, ref dtDetNum2);
               calculateMatrixDef(dtDetDen, ref dtDetDen2);

               double dblNumDet = calculateDet(dtDetNum2);
               double dblDenDet = calculateDet(dtDetDen2);

               double dblDet = 0;
               if (dblNumDet == 0 || dblDenDet == 0)
                   dblDet = 0;
               else
                   dblDet = Math.Abs((dblNumDet / dblDenDet) * 100);

               //Cargamos datos del calculados de las determinantes
               dblWeight += dblDet;
               loadDeterminateForElement(ref dtRecuperation, dgvData.Columns[j].Name.Trim(), dblDet);


           }

           //Calculamos relave y ley cabeza calculada

           loadRelaveAndCabeza(ref dtRecuperation, dblWeight, dblTms);
           //Cargamos leyes
           loadDataLeyLab(dgvData, ref dtRecuperation, intPosCol);

           //Calculamos Concentrado Metalico
           loadDataContentMetalico(dgvData, ref dtRecuperation, intPosCol);
           //Calculamos Distribucion
           loadDataDistribution(ref dtRecuperation, intPosCol);

           //Calculamos total de ensayo
           loadDataTotalEnsayo(ref dtRecuperation, intPosCol);

           //Total Recuperacion
           DataTable dtGranTotal = loadDataTotalRecuperataion(ref dtRecuperation, intPosCol);
           if (option == 1)
               return dtGranTotal;
           else
               return dtRecuperation;

       }




       private static DataTable  maxtrixDeterm(int intNumColumn)
       {
           DataTable dt = new DataTable();
          
           //Filas y Columnas
           for (int i = 0; i < intNumColumn; i++)
           {
               dt.Columns.Add(i+"", typeof(double));

               dt.Rows.Add();
           }


           return dt;
       }



       private static void loadMatrix(DataGridView dgvData, string strDistinctElement, int intPosCol, ref DataTable dtDetNum,int intPostColumnJ)
       {

           if (!dgvData.Columns[intPostColumnJ].Name.Trim().Equals(strDistinctElement) && !dgvData.Columns[intPostColumnJ].Name.Trim().Equals("Humedad") &&
               !dgvData.Columns[intPostColumnJ].Name.Trim().Equals("Fe") && !dgvData.Columns[intPostColumnJ].Name.Trim().Equals("S"))
               {
                   
                   int intCol = 0;
                   for(int w = 0; w<dgvData.Rows.Count;w++){
                       int intRow = 0;
                       if (dgvData.Rows[w].Cells[intPosCol-1].Value.ToString().Trim().IndexOf(dgvData.Columns[intPostColumnJ].Name.Trim())==-1)
                       {

                           for (int i = intPosCol; i < dgvData.Columns.Count; i++)
                           {
                               if (!dgvData.Columns[i].Name.Trim().Equals(strDistinctElement) && !dgvData.Columns[i].Name.Trim().Equals("Humedad") &&
                                   !dgvData.Columns[i].Name.Trim().Equals("Fe") && !dgvData.Columns[i].Name.Trim().Equals("S")
                                   )
                               {
                                   dtDetNum.Rows[intRow][intCol] = dgvData.Rows[w].Cells[i].Value;
                                   intRow++;
                               }
                               
                           }
                           intCol++;
                           
                       }
                   }
               }
       }



       private static void loadMatrixDen(DataGridView dgvData, string strDistinctElement, int intPosCol, ref DataTable dtDetNum, int intPostColumnJ)
       {

           if (!dgvData.Columns[intPostColumnJ].Name.Trim().Equals(strDistinctElement) && !dgvData.Columns[intPostColumnJ].Name.Trim().Equals("Humedad") &&
               !dgvData.Columns[intPostColumnJ].Name.Trim().Equals("Fe") && !dgvData.Columns[intPostColumnJ].Name.Trim().Equals("S"))
           {

               int intCol = 0;
               for (int w = 0; w < dgvData.Rows.Count; w++)
               {
                   int intRow = 0;
                   if (!dgvData.Rows[w].Cells[intPosCol - 1].Value.ToString().Trim().Equals("Cabeza"))
                   {

                       for (int i = intPosCol; i < dgvData.Columns.Count; i++)
                       {
                           if (!dgvData.Columns[i].Name.Trim().Equals(strDistinctElement) && !dgvData.Columns[i].Name.Trim().Equals("Humedad") &&
                            !dgvData.Columns[i].Name.Trim().Equals("Fe") && !dgvData.Columns[i].Name.Trim().Equals("S"))
                           {
                               dtDetNum.Rows[intRow][intCol] = dgvData.Rows[w].Cells[i].Value;
                               intRow++;
                           }

                       }
                       intCol++;

                   }
               }
           }
       }
       private static void loadEstructureRecuperation(ref DataTable dt)
       {
           //Realizamos la consulta de los elementos
           ViewQueries viewQueries = new ViewQueries();
           DataSet dts = viewQueries.getSPQueries("laboratory.sp_queries_element_con_by_type", null,1);
           dt.Columns.Clear();
           dt.Columns.Add("id",typeof(string));
           dt.Columns.Add("id_muestra", typeof(string));
           dt.Columns.Add("muestra", typeof(string));
           dt.Columns.Add("tms", typeof(double));
           dt.Columns.Add("peso", typeof(double));

           //Ensayo
           for (int i = 0; i < dts.Tables[0].Rows.Count; i++)
           {          
               dt.Columns.Add(dts.Tables[0].Rows[i]["cod_element_con"].ToString(),typeof(double));
           }

           //Contenido metalico
            for (int i = 0; i < dts.Tables[0].Rows.Count; i++)
           {
          
               dt.Columns.Add("_"+dts.Tables[0].Rows[i]["cod_element_con"].ToString(),typeof(double));
           }

            //Distribucion
            for (int i = 0; i < dts.Tables[0].Rows.Count; i++)
            {

                dt.Columns.Add("__"+dts.Tables[0].Rows[i]["cod_element_con"].ToString(), typeof(double));
            }

           dts.Tables[0].Clear();
           dts = viewQueries.getSPQueries("laboratory.sp_queries_product_all", null, "2");
           for (int i = 0; i < dts.Tables[0].Rows.Count; i++)
           {
               dt.Rows.Add("0", dts.Tables[0].Rows[i]["id_product"].ToString(), dts.Tables[0].Rows[i]["description"].ToString());
           }

           dt.Rows.Add("0", "", "CAB.CAL");

       }


       private static  void calculateMatrixDef(DataTable dtOrigen, ref DataTable dtSource){

           for (int i = 0; i < dtOrigen.Rows.Count-1; i++ )
           {
               for (int j = 0; j < dtOrigen.Columns.Count-1; j++)
               {
                   string strValue = dtOrigen.Rows[i][j + 1] != null ? dtOrigen.Rows[i][j + 1].ToString() : "0.00";
                   string strValue2 = dtOrigen.Rows[i][j] != null ? dtOrigen.Rows[i][j].ToString() : "0.00";
                   if(!strValue.Equals(""))
                       dtSource.Rows[i][j] = Convert.ToDouble(strValue) -Convert.ToDouble(strValue2);
               }


           }


       }


       private static double calculateDet(DataTable dt)
       {
           double dblDet = 0;
           string strValue=dt.Rows[0][0]!=null?dt.Rows[0][0].ToString():"0.00";
           if (strValue.Trim().Equals(""))
               return 0;
           dblDet =
           Convert.ToDouble(dt.Rows[0][0]) * (Convert.ToDouble(dt.Rows[1][1]) * Convert.ToDouble(dt.Rows[2][2]) - Convert.ToDouble(dt.Rows[2][1]) * Convert.ToDouble(dt.Rows[1][2])) +
           Convert.ToDouble(dt.Rows[1][0]) * (Convert.ToDouble(dt.Rows[2][1]) * Convert.ToDouble(dt.Rows[0][2]) - Convert.ToDouble(dt.Rows[0][1]) * Convert.ToDouble(dt.Rows[2][2])) +
           Convert.ToDouble(dt.Rows[2][0]) * (Convert.ToDouble(dt.Rows[0][1]) * Convert.ToDouble(dt.Rows[1][2]) - Convert.ToDouble(dt.Rows[1][1]) * Convert.ToDouble(dt.Rows[0][2]));


           return dblDet;
       }

       private static void loadDeterminateForElement(ref DataTable dt ,string strColumn, double dblDet){

          //double  dblPeso=0;
          //double dblTms = 0;
           for (int i = 0; i < dt.Rows.Count; i++)
           {
               if (dt.Rows[i]["muestra"].ToString().IndexOf(strColumn) != -1)
               {
                   dt.Rows[i]["peso"] = dblDet;
                  // dblWeight += dblDet;
                   dt.Rows[i]["tms"] = (Convert.ToDouble(dt.Rows[0]["tms"]) * dblDet) / Convert.ToDouble(dt.Rows[0]["peso"]);
                   //dblTms += (Convert.ToDouble(dt.Rows[0]["tms"]) * dblDet) / Convert.ToDouble(dt.Rows[0]["peso"]);

               }
           }
          // dt.Rows[dt.Rows.Count - 1]["peso"] = Convert.ToDouble(dt.Rows[dt.Rows.Count - 1]["peso"].ToString().Equals("")?"0":dt.Rows[dt.Rows.Count - 1]["peso"]) + dblPeso;
          // dt.Rows[dt.Rows.Count - 1]["tms"] = Convert.ToDouble(dt.Rows[dt.Rows.Count - 1]["tms"].ToString().Equals("") ? "0" : dt.Rows[dt.Rows.Count - 1]["tms"]) + dblTms;
       }


       private static void loadDataLeyLab(DataGridView dgvLeyLab, ref  DataTable dtRecuperation, int intPosCol ){

           for (int i = 0; i < dgvLeyLab.Rows.Count; i++)
           {
               for (int j = intPosCol; j < dgvLeyLab.Columns.Count; j++)
               {
                   int intCol = getPosition(dtRecuperation, dgvLeyLab.Columns[j].Name);
                   if(intCol!=-1)
                     dtRecuperation.Rows[i][intCol] = dgvLeyLab.Rows[i].Cells[j].Value;

               }

           }
       }

       private static void loadDataContentMetalico(DataGridView dgvLeyLab, ref  DataTable dtRecuperation, int intPosCol)
       {

           for (int i = 0; i < dgvLeyLab.Rows.Count; i++)
           {
               for (int j = intPosCol; j < dgvLeyLab.Columns.Count; j++)
               {
                   int intCol = getPosition(dtRecuperation, "_"+dgvLeyLab.Columns[j].Name);
                   if (intCol != -1)
                   {
                       double dblValue=0;
                       if(dgvLeyLab.Columns[j].Name.Equals("Ag"))
                           dblValue = Convert.ToDouble(dgvLeyLab.Rows[i].Cells[j].Value) * Convert.ToDouble(dtRecuperation.Rows[i]["tms"]);
                       else
                           dblValue = (Convert.ToDouble(dgvLeyLab.Rows[i].Cells[j].Value) * Convert.ToDouble(dtRecuperation.Rows[i]["tms"])) / 100;

                        dtRecuperation.Rows[i][intCol]=dblValue;
                       
                       if(i>0)
                        dtRecuperation.Rows[dtRecuperation.Rows.Count - 1][intCol] = Convert.ToDouble(
                            dtRecuperation.Rows[dtRecuperation.Rows.Count - 1][intCol].ToString().Equals("") ? "0" : dtRecuperation.Rows[dtRecuperation.Rows.Count - 1][intCol]) +
                            dblValue;


                   }
               }

           }
       }

       public static void loadDataDistribution(ref  DataTable dtRecuperation, int intPosCol)
       {

           for (int i = 0; i < dtRecuperation.Rows.Count-1; i++)
           {
               for (int j = intPosCol; j < dtRecuperation.Columns.Count; j++)
               {
                   if (dtRecuperation.Columns[j].ColumnName.IndexOf("_") !=-1)
                   {


                       int intCol = getPosition(dtRecuperation, "_" + dtRecuperation.Columns[j].ColumnName);
                       if (intCol != -1)
                       {
                           if (i == 0)
                           {
                               dtRecuperation.Rows[i][intCol] = 100;
                               dtRecuperation.Rows[dtRecuperation.Rows.Count - 1][intCol] = 100;
                               continue;
                           }


                           double dblValue = 0;
                           if (dtRecuperation.Columns[j].ColumnName.Equals("_Ag"))
                           {
                               dblValue =(Convert.ToDouble(dtRecuperation.Rows[i][j]) / Convert.ToDouble(dtRecuperation.Rows[dtRecuperation.Rows.Count-1][j]))*100;
                           }
                           else
                           {
                               dblValue = (Convert.ToDouble(dtRecuperation.Rows[i][j]) / Convert.ToDouble(dtRecuperation.Rows[0][j])) * 100;
                           }

                           dtRecuperation.Rows[i][intCol] = dblValue;

                           //if (i > 0)
                           //    dtRecuperation.Rows[dtRecuperation.Rows.Count - 1][intCol] = Convert.ToDouble(
                           //        dtRecuperation.Rows[dtRecuperation.Rows.Count - 1][intCol].ToString().Equals("") ? "0" : dtRecuperation.Rows[dtRecuperation.Rows.Count - 1][intCol]) +
                           //        dblValue;


                       }
                   }
               }

           }
       }


       public static void loadDataTotalEnsayo(ref  DataTable dtRecuperation, int intPosCol)
       {

           
               for (int j = intPosCol; j < dtRecuperation.Columns.Count; j++)
               {
                   if (dtRecuperation.Columns[j].ColumnName.IndexOf("_") != -1)
                   {

                       if (dtRecuperation.Columns[j].ColumnName.Trim().Substring(1, dtRecuperation.Columns[j].ColumnName.Trim().Length - 1).IndexOf("_") !=-1)
                           return;

                       int intCol = getPosition(dtRecuperation, dtRecuperation.Columns[j].ColumnName.Trim().Substring(1,dtRecuperation.Columns[j].ColumnName.Trim().Length-1));
                       if (intCol != -1)
                       {

                           if (dtRecuperation.Columns[j].ColumnName.Trim().Equals("_Ag"))
                               dtRecuperation.Rows[dtRecuperation.Rows.Count - 1][intCol] =
                                  (Convert.ToDouble(dtRecuperation.Rows[dtRecuperation.Rows.Count - 1][j]) / Convert.ToDouble(dtRecuperation.Rows[dtRecuperation.Rows.Count - 1]["tms"]));
                           else
                               dtRecuperation.Rows[dtRecuperation.Rows.Count - 1][intCol] =
                               (Convert.ToDouble(dtRecuperation.Rows[dtRecuperation.Rows.Count - 1][j]) / Convert.ToDouble(dtRecuperation.Rows[dtRecuperation.Rows.Count - 1]["tms"])) * 100;

                                   


                       }
                   }
               }

           
       }


       public static void loadDataTotalEnsayo(ref  DataTable dtRecuperation, int intPosCol, int intRowDef)
       {


           for (int j = intPosCol; j < dtRecuperation.Columns.Count; j++)
           {
               if (dtRecuperation.Columns[j].ColumnName.IndexOf("_") != -1)
               {

                   if (dtRecuperation.Columns[j].ColumnName.Trim().Substring(1, dtRecuperation.Columns[j].ColumnName.Trim().Length - 1).IndexOf("_") != -1)
                       return;

                   int intCol = getPosition(dtRecuperation, dtRecuperation.Columns[j].ColumnName.Trim().Substring(1, dtRecuperation.Columns[j].ColumnName.Trim().Length - 1));
                   if (intCol != -1)
                   {

                       if (dtRecuperation.Columns[j].ColumnName.Trim().Equals("_Ag"))
                           dtRecuperation.Rows[intRowDef][intCol] =
                              (Convert.ToDouble(dtRecuperation.Rows[intRowDef][j]) / Convert.ToDouble(dtRecuperation.Rows[intRowDef]["tms"]));
                       else
                           dtRecuperation.Rows[intRowDef][intCol] =
                           (Convert.ToDouble(dtRecuperation.Rows[intRowDef][j]) / Convert.ToDouble(dtRecuperation.Rows[intRowDef]["tms"])) * 100;




                   }
               }
           }


       }



       private static DataTable loadDataTotalRecuperataion(ref  DataTable dtRecuperation, int intPosCol)
       {

           DataTable dtGranTotalRec = new DataTable();
           ViewQueries viewQueries = new ViewQueries();
           DataSet dts = viewQueries.getSPQueries("laboratory.sp_queries_element_con_by_type", null, 1);
           for (int i = 0; i < dts.Tables[0].Rows.Count; i++)
           {
               dtGranTotalRec.Columns.Add(dts.Tables[0].Rows[i]["cod_element_con"].ToString(), typeof(double));
           }

           dtGranTotalRec.Rows.Add();

           //for (int i = 0; i < dtRecuperation.Rows.Count; i++)
           //{

               int w = 0;
               for (int j = intPosCol; j < dtRecuperation.Columns.Count; j++)
               {
                   if (dtRecuperation.Columns[j].ColumnName.IndexOf("__") != -1)
                   {

                       string strCode = dtRecuperation.Columns[j].ColumnName.Trim().Substring(2, dtRecuperation.Columns[j].ColumnName.Trim().Length - 2);

                       int intCol = getPosition(dtRecuperation, dtRecuperation.Columns[j].ColumnName.Trim());
                       if (intCol != -1)
                       {
                           int intColRec = getPosition(dtGranTotalRec, strCode);


                           if (dtRecuperation.Columns[j].ColumnName.Trim().Equals("__Ag"))
                           {
                               w++;
                               dtGranTotalRec.Rows[0][intColRec] = dtRecuperation.Rows[2][j];
                               //break;
                           }
                           else
                           {
                               
                               dtGranTotalRec.Rows[0][intColRec] = dtRecuperation.Rows[w][j];
                              // break;
                               w++;
                           }
                       }
                   }




               }

               
          // }


               return dtGranTotalRec;
       }

       private static void loadRelaveAndCabeza(ref DataTable dtRecuperation , double dblWeight, double dblTms){

           dtRecuperation.Rows[dtRecuperation.Rows.Count - 2]["tms"] = dblTms * (100-dblWeight) / 100;
           dtRecuperation.Rows[dtRecuperation.Rows.Count - 2]["peso"] =(100- dblWeight);

           dtRecuperation.Rows[dtRecuperation.Rows.Count - 1]["tms"] = dblTms;
           dtRecuperation.Rows[dtRecuperation.Rows.Count - 1]["peso"] = 100;


       }

       public static int verifiedTotalDayMounth(int intMounth, int intYear)
       {
           if (intMounth == 1 || intMounth == 3 || intMounth == 5 || intMounth == 7 || intMounth == 8 || intMounth == 10 || intMounth == 12)
               return 31;
           else if (intMounth == 2)
           {
               if (intYear % 4==0 && intYear%100!=0 || intYear%400==0)//verifica si el anio es bisiesto

                   return 29;
               else
                   return 28;
           }
           else
               return 30;


       }



       public static void copypageDgv(ref DataGridView dgv, int intRowIndex, int intPos, string user)
       {

           IDataObject data = Clipboard.GetDataObject();
           int j = intPos;
           if (!data.GetDataPresent("CSV", false)) return;
           try
           {

               string strCad = Clipboard.GetText();
               char[] chr = { '\r', '\n' };
               string[] lines = strCad.Split(chr, StringSplitOptions.RemoveEmptyEntries);

               foreach (string line in lines)
               {

                   string[] cells = line.Split('\t');

                   //Ciclo para cada linea del copy 
                   foreach (string cell in cells)
                   {
                       if (j == 0)
                           dgv.Rows[intRowIndex].Cells[j].Value = 0;
                       else
                           dgv.Rows[intRowIndex].Cells[j].Value = cell;
                       j++;

                   }

               }

           }
           catch (Exception ex)
           {
               Log.eLog(user, "(ERROR): Se obtuvo errores al intentar copiar datos a grilla:" + ex.ToString());
           }
       }


       public static void copypageDgvAll(ref DataGridView dgv, int intRowIndex, int intPos, string user)
       {

           IDataObject data = Clipboard.GetDataObject();
           
           int i = 0;
           if (!data.GetDataPresent("CSV", false)) return;
           try
           {

               string strCad = Clipboard.GetText();
               char[] chr = { '\r', '\n' };
               string[] lines = strCad.Split(chr, StringSplitOptions.RemoveEmptyEntries);

               foreach (string line in lines)
               {
                  
                   string[] cells = line.Split('\t');
                   int j = intPos;
                   //Ciclo para cada linea del copy 
                   foreach (string cell in cells)
                   {
                       if (j == 0)
                           dgv.Rows[i].Cells[j].Value = 0;
                       else
                           dgv.Rows[i].Cells[j].Value = cell;
                       j++;

                   }
                   i++;

               }

           }
           catch (Exception ex)
           {
               Log.eLog(user, "(ERROR): Se obtuvo errores al intentar copiar datos a grilla:" + ex.ToString());
           }
       }


       //Validando codigo de  Codigo Tabla
       public static bool getValidate(string strNameTabe, string srtNameField, String strValue, string strUser)
       {
           ConnectionDB connectionDB = new ConnectionDB();
           NpgsqlConnection connection = connectionDB.Connecction();
           try
           {

               connection.Open();
               NpgsqlCommand command = connection.CreateCommand();
               NpgsqlDataReader Reader;
               command.CommandText = "SELECT * FROM " + strNameTabe + " WHERE " + srtNameField + "=" + (isnumeric(strValue)
                   ? strValue : "'" + strValue + "'");

               Log.eLog(strUser, "Sentencia SQL:" + command.CommandText);
               Reader = command.ExecuteReader();
               bool value = false;

               while (Reader.Read())
               {
                   value = true;

               }


               return value;

           }
           catch (Exception e)
           {
               Log.eLog(strUser, "ERROR: al consultar " + strNameTabe + ":" + e);
               return false;
           }
           finally
           {
               if (connection != null)
                   connection.Close();
           }

       }


       public static void getConsProbUS(string strFirstLetter, ref string strCons, ref string strProb)
       {

           if (strFirstLetter.Trim().Equals("ALTA 9"))
           {
               strCons = "E"; strProb = "1";
           }
           else if (strFirstLetter.Trim().Equals("ALTA 16"))
           {
               strCons = "E"; strProb = "2";
           }
           else if (strFirstLetter.Trim().Equals("EXTREMA 20"))
           {
               strCons = "E"; strProb = "3";
           }
           else if (strFirstLetter.Trim().Equals("EXTREMA 23"))
           {
               strCons = "E"; strProb = "4";
           }
           else if (strFirstLetter.Trim().Equals("EXTREMA 25"))
           {
               strCons = "E"; strProb = "5";
           }
           else if (strFirstLetter.Trim().Equals("MODERADA 6"))
           {
               strCons = "D"; strProb = "1";
           }
           else if (strFirstLetter.Trim().Equals("ALTA 13"))
           {
               strCons = "D"; strProb = "2";
           }
           else if (strFirstLetter.Trim().Equals("ALTA 18"))
           {
               strCons = "D"; strProb = "3";
           }
           else if (strFirstLetter.Trim().Equals("EXTREMA 22"))
           {
               strCons = "D"; strProb = "4";
           }
           else if (strFirstLetter.Trim().Equals("EXTREMA 24"))
           {
               strCons = "D"; strProb = "5";
           }
           else if (strFirstLetter.Trim().Equals("BAJA 4"))
           {
               strCons = "C"; strProb = "1";
           }
           else if (strFirstLetter.Trim().Equals("MODERADA 11"))
           {
               strCons = "C"; strProb = "2";
           }
           else if (strFirstLetter.Trim().Equals("ALTA 15"))
           {
               strCons = "C"; strProb = "3";
           }
           else if (strFirstLetter.Trim().Equals("EXTREMA 19"))
           {
               strCons = "C"; strProb = "4";
           }
           else if (strFirstLetter.Trim().Equals("EXTREMA 21"))
           {
               strCons = "C"; strProb = "5";
           }
           else if (strFirstLetter.Trim().Equals("BAJA 2"))
           {
               strCons = "B"; strProb = "1";
           }
           else if (strFirstLetter.Trim().Equals("BAJA 7"))
           {
               strCons = "B"; strProb = "2";
           }
           else if (strFirstLetter.Trim().Equals("MODERADA 12"))
           {
               strCons = "B"; strProb = "3";
           }
           else if (strFirstLetter.Trim().Equals("ALTA 14"))
           {
               strCons = "B"; strProb = "4";
           }
           else if (strFirstLetter.Trim().Equals("EXTREMA 17"))
           {
               strCons = "B"; strProb = "5";
           }
           else if (strFirstLetter.Trim().Equals("BAJA 1"))
           {
               strCons = "A"; strProb = "1";
           }
           else if (strFirstLetter.Trim().Equals("BAJA 3"))
           {
               strCons = "A"; strProb = "2";
           }
           else if (strFirstLetter.Trim().Equals("MODERADA 5"))
           {
               strCons = "A"; strProb = "3";
           }
           else if (strFirstLetter.Trim().Equals("ALTA 8"))
           {
               strCons = "A"; strProb = "4";
           }
           else if (strFirstLetter.Trim().Equals("ALTA 10"))
           {
               strCons = "A"; strProb = "5";
           }
       }



    }
}
